Performance

From: sofresud <sofresud_at_wanadoo.fr>
Date: Mon, 13 Dec 1999 18:24:35 +0100
Message-ID: <8339ad$nnc$1@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 Mon Dec 13 1999 - 11:24:35 CST