Re: Index Range Scans....

From: Miggins <mtproc_at_yahoo.co.uk>
Date: 3 Mar 2006 07:30:59 -0800
Message-ID: <1141399859.469319.248780@u72g2000cwu.googlegroups.com>

Gents,

I have discovered the reason for the HASH JOIN.

The LOG_DETAILS table has slightly fewer rows than the LOGS table. So the optimizer is using this, the smaller table to build the HASH table. At this point it doesnt know all the data to use the PK as the information comes from LOGS so it uses a range scan. It then uses the HASH table to scan the LOGS table, again only know part of the primary key...its taking far too long to run.

My choices are to split the query into 2 separate cursors or try and get HINTS to work.....anyone see why my hints are being ignored?

Tried both these....

SELECT /*+ USE_NL(L) */ SELECT /*+ NO_USE_HASH USE_NL(L) */ Received on Fri Mar 03 2006 - 09:30:59 CST