Re: Slow query

astalavista wrote:
> Hi,
>
> Is it possible to speed up this query ?
> Thanks in advance
> 9.2.0.6
>
> 2 SELECT dc0.nrid, dc0.so0_nrid, dc0.etat,
> 3 TO_CHAR (dc0.date_creat, 'DD/MM/YYYY') "dc0.date_creat",
> 4 w.remark "Modif Groupement", y.remark "Ecart Coop",
> 5 u.remark "Nom Précommande", z.remark "Amex", x.remark
> "Commentaires",
> 6 TO_CHAR (dc0.dat5, 'DD/MM/YYYY') "dc0.dat5", dc0.REF,
> dc0.netat,
> 7 so0.var53 "CIP", so0.societe "Pharmacie", so0.code_post "CP",
> 8 so0.loc "Ville", so0.concur "VIP", so0.var18 "Segment",
> 9 dc0.titulaire "Preneur ordre", depos.societe "Dépositaire
> Cde",
> 10 TRUNC (dc0.num4, 2) "CA Cde", t.remark "Qté Cde",
> 11 TRUNC (dc0.num12, 2) "P.C.", dc0.etat, dc0.code_dep, dc0.niv,
> 12 dc0.TYPE, dc0.netat
> 13 FROM bcrm.dc0 dc0,
> 14 bcrm.so0 so0,
> 15 bcrm.so0 depos,
> 16 (SELECT /*+ first_rows index(dc6) */
> 17 dc0_nrid, ap01_name, remark
> 18 FROM bcrm.dc6) z,
> 19 (SELECT /*+ first_rows index(dc6) */
> 20 dc0_nrid, ap01_name, remark
> 21 FROM bcrm.dc6) y,
> 22 (SELECT /*+ first_rows index(dc6) */
> 23 dc0_nrid, ap01_name, remark
> 24 FROM bcrm.dc6) x,
> 25 (SELECT /*+ first_rows index(dc6) */
> 26 dc0_nrid, ap01_name, remark
> 27 FROM bcrm.dc6) w,
> 28 (SELECT /*+ first_rows index(dc6) */
> 29 dc0_nrid, ap01_name, remark
> 30 FROM bcrm.dc6) u,
> 31 (SELECT /*+ first_rows index(dc6) */
> 32 dc0_nrid, ap01_name, remark
> 33 FROM bcrm.dc6) t
> 34 WHERE dc0.so0_nrid = so0.nrid
> 35 AND dc0.TEMPLATE IS NULL
> 36 AND TO_NUMBER (dc0.var22) = depos.nrid(+)
> 37 AND dc0.nrid = t.dc0_nrid(+)
> 38 AND t.ap01_name = 'Qté Totale Cdée'
> 39 AND dc0.nrid = z.dc0_nrid(+)
> 40 AND z.ap01_name(+) = 'Commentaire : AMEX'
> 41 AND dc0.nrid = y.dc0_nrid(+)
> 42 AND y.ap01_name(+) = 'Commentaire : Modif. PC'
> 43 AND dc0.nrid = x.dc0_nrid(+)
> 44 AND x.ap01_name = 'Commentaire'
> 45 AND dc0.nrid = w.dc0_nrid(+)
> 46 AND w.ap01_name(+) = 'Commentaire : Modif. Gpt'
> 47 AND dc0.nrid = u.dc0_nrid(+)
> 48 AND u.ap01_name(+) = 'Commentaire : Précommande'
> 49 AND dc0.etat IN
> 50 ('A valider par SCLI', 'Validée par SCLI', 'A compléter par
> SCOM')
> 51 AND EXISTS (
> 52 SELECT NULL
> 53 FROM am0 am0_1
> 54 WHERE am0_1.titulaire = :1
> 55 AND UPPER (am0_1.fonction) LIKE '%S%CLIENT%')
> 56 ORDER BY 3, 12, 8, 9, 10, 11, 13 DESC
> 57 /
>
> ----------------------------------------------------------------------------------------------------
> | Id | Operation | Name
> | Rows | Bytes |TempSpc| Cost |
> ----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT |
> | 196K| 85M| | 51394 |
> | 1 | SORT ORDER BY |
> | 196K| 85M| 180M| 51394 |
> |* 2 | FILTER |
> | | | | |
> | 3 | TABLE ACCESS BY INDEX ROWID | DC6
> | 2 | 62 | | 1 |
> | 4 | NESTED LOOPS |
> | 196K| 85M| | 38123 |
> | 5 | NESTED LOOPS |
> | 123K| 49M| | 31970 |
> | 6 | NESTED LOOPS OUTER |
> | 108K| 40M| | 26558 |
> | 7 | NESTED LOOPS OUTER |
> | 99237 | 34M| | 21597 |
> | 8 | NESTED LOOPS OUTER |
> | 91033 | 28M| | 17045 |
> | 9 | NESTED LOOPS OUTER |
> | 83582 | 23M| | 12866 |
> | 10 | NESTED LOOPS OUTER |
> | 76786 | 19M| | 9027 |
> | 11 | NESTED LOOPS |
> | 76786 | 15M| | 5187 |
> | 12 | INLIST ITERATOR |
> | | | | |
> |* 13 | TABLE ACCESS BY INDEX ROWID | DC0
> | 76786 | 8323K| | 1347 |
> | 14 | BITMAP CONVERSION TO ROWIDS|
> | | | | |
> |* 15 | BITMAP INDEX SINGLE VALUE | INDB_DC0_ETAT
> | | | | |
> | 16 | TABLE ACCESS BY INDEX ROWID | SO0
> | 1 | 107 | | 1 |
> |* 17 | INDEX UNIQUE SCAN | PK_SO0NRID
> | 1 | | | |
> | 18 | TABLE ACCESS BY INDEX ROWID | SO0
> | 1 | 51 | | 1 |
> |* 19 | INDEX UNIQUE SCAN | PK_SO0NRID
> | 1 | | | |
> | 20 | TABLE ACCESS BY INDEX ROWID | DC6
> | 1 | 31 | | 1 |
> |* 21 | INDEX RANGE SCAN |
> IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
> | 22 | TABLE ACCESS BY INDEX ROWID | DC6
> | 1 | 31 | | 1 |
> |* 23 | INDEX RANGE SCAN |
> IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
> | 24 | TABLE ACCESS BY INDEX ROWID | DC6
> | 1 | 31 | | 1 |
> |* 25 | INDEX RANGE SCAN |
> IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
> | 26 | TABLE ACCESS BY INDEX ROWID | DC6
> | 1 | 31 | | 1 |
> |* 27 | INDEX RANGE SCAN |
> IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
> | 28 | TABLE ACCESS BY INDEX ROWID | DC6
> | 1 | 31 | | 1 |
> |* 29 | INDEX RANGE SCAN |
> IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
> |* 30 | INDEX RANGE SCAN |
> IND_DC6_DC0_NRID_AP01_NAME | 1 | | | |
> |* 31 | TABLE ACCESS BY INDEX ROWID | AM0
> | 1 | 29 | | 1 |
> |* 32 | INDEX UNIQUE SCAN | IND_AM0_TITULAIRE
> | 1 | | | 1 |
> ----------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter( EXISTS (SELECT /*+ */ 0 FROM "BXAT"."AM0" "AM0_1" WHERE
> "AM0_1"."TITULAIRE"=:Z AND
> UPPER("AM0_1"."FONCTION") LIKE '%S%CLIENT%'))
> 13 - filter("DC0"."TEMPLATE" IS NULL)
> 15 - access("DC0"."ETAT"='A compléter par SCOM' OR "DC0"."ETAT"='A valider
> par SCLI' OR "DC0"."ETA
> par SCLI')
> 17 - access("DC0"."SO0_NRID"="SO0"."NRID")
> 19 - access("DEPOS"."NRID"(+)=TO_NUMBER("DC0"."VAR22"))
> 21 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
> "DC6"."AP01_NAME"(+)='Commentaire : Modif. PC')
> 23 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
> "DC6"."AP01_NAME"(+)='Commentaire : AMEX')
> 25 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
> "DC6"."AP01_NAME"(+)='Commentaire : Modif. Gpt')
> 27 - access("DC0"."NRID"="DC6"."DC0_NRID"(+) AND
> "DC6"."AP01_NAME"(+)='Commentaire : Précommande')
> 29 - access("DC0"."NRID"="DC6"."DC0_NRID" AND
> "DC6"."AP01_NAME"='Commentaire')
> 30 - access("DC0"."NRID"="DC6"."DC0_NRID" AND "DC6"."AP01_NAME"='Qté
> Totale Cdée')
> 31 - filter(UPPER("AM0_1"."FONCTION") LIKE '%S%CLIENT%')
> 32 - access("AM0_1"."TITULAIRE"=:Z)
>
> Note: cpu costing is off
>
> 59 rows selected.
>
> Elapsed: 00:00:00.08
>
>
> --------------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.524 / Virus Database: 270.4.3/1526 - Release Date: 30/06/2008
> 08:43

If the WITH clause exists in your version then you might want to replace all of these:
SELECT /*+ first_rows index(dc6) */ dc0_nrid, ap01_name, remark FROM bcrm.dc6)
with a single query.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org