Home » RDBMS Server » Performance Tuning » Please help tuning SQL (Oracle 9i)
Please help tuning SQL [message #320934] Fri, 16 May 2008 15:31 Go to next message
Messages: 180
Registered: June 2000
Senior Member

I'm having a hard time trying to push Oracle to execute a particular query the way I want...

Oracle9i Enterprise Edition Release - 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:
L.ID (Primary Key)

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 #320955 is a reply to message #320934] Sat, 17 May 2008 00:27 Go to previous messageGo to next message
Messages: 66
Registered: March 2008
and b.col_b in

Instead of using "in" you can try using exists clause
and include c.child=b.col_b (? instead of c.child=u.id, because
"b.col_b in" implies b.col_b=c.child)



[Updated on: Sat, 17 May 2008 00:34]

Report message to a moderator

Re: Please help tuning SQL [message #321210 is a reply to message #320955] Mon, 19 May 2008 07:50 Go to previous message
Messages: 3727
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
Previous Topic: How To Get Previous Version Of Stats Gathered
Next Topic: How i tune database and sql
Goto Forum:

Current Time: Mon Mar 27 23:25:50 CDT 2017

Total time taken to generate the page: 0.12445 seconds