SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

HASH JOIN or NESTED LOOP

WilhelmSep 5 2007 — edited Sep 5 2007

I've been asked to check if HASH JOIN is more suitable than NESTED LOOP(which CBO chose by default) for the following query.

SELECT CM_DETAILS.TASK_ID FROM GEN_TYPE, CM_DETAILS WHERE ( ( ( ( ( CM_DETAILS.STAT_CODE < 8 ) AND ( GEN_TYPE.TASK_ID = CM_DETAILS.TASK_ID ) ) AND ( GEN_TYPE.DEST_LOCN_ID = 5 ) ) AND ( GEN_TYPE.COM_ID = 7 ) ) AND ( ( ( CM_DETAILS.CASE_NO = 1 ) OR ( CM_DETAILS.CASE_NO = 3 ) ) OR ( CM_DETAILS.CASE_NO = 9 ) ) )

Both GEN_TYPE and CM_DETAILS tables have over 330,000 rows.
Version: 10g R2

Any thoughts?

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 3 2007

Added on Sep 5 2007

6 comments

824 views