Re: Performance

From: <argosy22_at_my-deja.com>
Date: Wed, 05 Jan 2000 21:05:18 GMT
Message-ID: <850bm2$g3c$1@nnrp1.deja.com>

HI,

Use a GROUP BY instead.
DISTINCT can be really slow sometimes,
especially with joins.

 SELECT T1.FK0,T1.FK00,T3.PK3,T1.VALUE
 FROM
   T1,T2,T3,T4,T5
 WHERE ... (joins and filters)
GROUP BY T1.FK0,T1.FK00,T3.PK3,T1.VALUE ORDER BY 1,2 Argosy

In article <8339ad$nnc$1_at_wanadoo.fr>,
  "sofresud" <sofresud_at_wanadoo.fr> wrote:
>
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 15:05:18 CST