Put non-unique indexes on T1.FK0 and T1.FK00 if they aren't already indexed.
It is often good to put indexes on foreign keys anyway. I think the "order
by" is causing a sort.
sofresud wrote in message <8339ad$nnc$1_at_wanadoo.fr>...
>
>I got some performance problems,
>In fact the SQL statement is inside a PL/SQL procedure
>This is my SQL Select Statement i would like to improve :
>Any help is very welcome
>
>
>SELECT DISTINCT T1.FK0,T1.FK00,T3.PK3,T1.VALUE
>FROM
> T1,T2,T3,T4,T5
>WHERE
> T5.PK5 = :X5 AND T2.FK5 = T5.PK5 AND
> T2.FK0 = T1.FK0 AND T5.PK5 = T3.FK5
> AND T4.PK4 = T5.FK4 AND
> (T1.FK0 > :X0 OR (T1.FK0 = :X0 AND T1.FK00 > :X1 ))
> AND (T3.FK00 = T1.FK00 OR T4.FK00 = T1.FK00) ORDER BY 1,2
>
>
>TKPROF statistics
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ---------- -----
-
>----
>Parse 1 0.00 0.00 0 0 0
>0
>Execute 43 0.69 1.35 0 0 33
>0
>Fetch 21938 409.16 412.71 0 36489297 13559
>21937
>------- ------ -------- ---------- ---------- ---------- ---------- -----
-
>----
>total 21982 409.85 414.06 0 36489297 13592
>21937
>
> As you see the Ratio rows / query is very small ....
> I would like to see the number of query decrease..
>
>
>
>Explain Plan :
>
>OPERATION OPTIONS OBJECT_NAME ID pere POSITION
>------------ ------------ ------------ --- ----- ---------
>SORT UNIQUE 1 0 1
>NESTED LOOPS 2 1 1
>NESTED LOOPS 3 2 1
>NESTED LOOPS 4 3 1
>NESTED LOOPS 5 4 1
>TABLE ACCESS BY ROWID T5 6 5 1
>INDEX UNIQUE SCAN PK5 7 6 1
>TABLE ACCESS BY ROWID T4 8 5 2
>INDEX UNIQUE SCAN PK4 9 8 1
>TABLE ACCESS BY ROWID T2 10 4 2
>INDEX RANGE SCAN FK5 11 10 1
>TABLE ACCESS BY ROWID T3 12 3 2
>INDEX RANGE SCAN FK5 13 12 1
>TABLE ACCESS BY ROWID T1 14 2 2
>INDEX RANGE SCAN FK0 15 14 1
>
>
>Maybe, I should try to a complete opposite way, but i don't know which
>one.... any suggestion ? Thank you
>
>
>
>
Received on Tue Dec 14 1999 - 22:52:34 CST
