What do I look for if I want to find out why the server prefers a nested loop over hash join?
The server is 10.2.0.4.0.
The query is:
SELECT p.*
FROM t1 p, t2 d
WHERE d.emplid = p.id_psoft
AND p.flag_processed = 'N'
AND p.desc_pool = :b1
AND NOT d.name LIKE '%DUPLICATE%'
AND ROWNUM < 2
tkprof output is:
Production
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 4 0
Execute 1 0.00 0.01 0 4 0 0
Fetch 1 228.83 223.48 0 4264533 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 228.84 223.50 0 4264537 4 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 108 (SANJEEV)
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=4264533 pr=0 pw=0 time=223484076 us)
1 NESTED LOOPS (cr=4264533 pr=0 pw=0 time=223484031 us)
10401 TABLE ACCESS FULL T1 (cr=192 pr=0 pw=0 time=228969 us)
1 TABLE ACCESS FULL T2 (cr=4264341 pr=0 pw=0 time=223182508 us)
Development
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.01 0 4 0 0
Fetch 1 0.05 0.03 0 512 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.06 0.06 0 516 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 113 (SANJEEV)
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=512 pr=0 pw=0 time=38876 us)
1 HASH JOIN (cr=512 pr=0 pw=0 time=38846 us)
51 TABLE ACCESS FULL T2 (cr=492 pr=0 pw=0 time=30230 us)
861 TABLE ACCESS FULL T1 (cr=20 pr=0 pw=0 time=2746 us)