First page Back Continue Last page Graphics

USE_HASH Hint

Notes:

By default, CBO chose a nested loops join for this query. The USE_HASH hint forced the optimizer to chose a hash join. CBO correctly selected the SMALL table as the driver. Use the ORDERED hint if you would like to control which table is selected as the driver.
Regarding performance, this TKPROF excerpt shows that the nested loops statistics were:
call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0
Execute 1 0.00 0.00 0 0
Fetch 889 0.04 0.08 0 2334
------- ------ -------- ---------- ---------- ----------
total 891 0.04 0.08 0 2334
Given that the nested loops produced less logical I/O, CPU and elapsed time, seems that we should let CBO make the decision and not use the USE_HASH hint in this case.