Please help tuning SQL [message #320934] |
Fri, 16 May 2008 15:31 |
Ivan
Messages: 180 Registered: June 2000
|
Senior Member |
|
|
Greetings!
I'm having a hard time trying to push Oracle to execute a particular query the way I want...
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
I won't paste the actual query here, as it's too lengthy, but a summary of it would look like this:
select u.id
from u
where not exists
(
select 1
from b, l, ld
where l.id = b.col_a
and ld.col_e = l.id
and l.col_d = 'A'
and ld.col_g = 'B'
and ld.col_h = 'C'
and b.col_b in
(
select c.child
from c
connect by prior c.parent = c.child
start with c.child = u.id -- <<<=== referencing the outer query
)
)
Column U.ID has an index, so a INDEX FAST FULL SCAN on it is fine.
The following columns are also indexed:
B.COL_A
L.ID (Primary Key)
LD.COL_E
L.COL_D
LD.COL_G
B.COL_B
C.CHILD, PARENT (Unique)
The 'A', 'B', and 'C' are arbitrary string values.
I want the next step in the execution plan to be the "connect by" sub-query as it would serve as input for the "not exists" sub-query.
Instead Oracle does a INDEX RANGE SCAN on LD.COL_G, then accesses the LD table by INDEX ROWID, then a INDEX UNIQUE SCAN on L.ID, then accesses the L table by INDEX ROWID, then a NESTED LOOPS between the two results, and only then goes to access the index on B.COL_A...
Only after that it's executing the "connect by" query (correctly) and finally the FILTER.
I tried using the PUSH_SUBQ hint in both sub-queries - didn't work.
Moving the "connect by" sub-query to the FROM clause of the "not exists" sub-query is not possible, because in that case column U.ID (from the "connect by" sub-query) will not be a "valid identifier"
I tried suppressing indexes in both L and LD tables by concatenating their columns with NULLs, but that only triggered FULL TABLE SCANs on them, without modifying the sequence of the tables being accessed.
Any help would be appreciated.
Thank you in advance.
|
|
|
|
Re: Please help tuning SQL [message #321210 is a reply to message #320955] |
Mon, 19 May 2008 07:50 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Find the Explain Plan step that encloses the CONNECT BY and look at the parent step. It should be a join - either NESTED LOOPS or HASH.
- If it is NESTED LOOPS, make sure that the CONNECT BY sub-query is the FIRST sub-step
- If it is a HASH join, it will be OK
- If it is FILTER or NESTED LOOPS with the CONNECT BY as the second step, you have a problem.
Secondly, find the step that performs the FAST FULL index scan on U, then find the parent step. If it is a HASH JOIN (ANTI), no problem. It it is a FILTER with U as the first step, problem.
Tell me what you find.
Ross Leishman
|
|
|