Optimize query ...

From: astalavista <nobody_at_nowhere.com>
Date: Thu, 18 Jan 2007 21:06:32 +0100
Message-ID: <45afd346$0$22820$426a74cc@news.free.fr>

Can you help me to optimize this query ( 46 min on 9.2.0.4) thanks in advance ...

SELECT a.mois, DBMS_UTILITY.get_hash_value (a.uga, 1, POWER (2, 30)) AS uga,
3 SUM (a.caxv) caxv, SUM (a.unxv * c.coepdt) unxv, SUM (a.pxv) pxv,
4 SUM (a.caxh) caxh, SUM (a.unxh * c.coepdt) unxh, SUM (a.pxh) pxh,

  5           SUM (DECODE (b.caxv, NULL, 0, b.caxv)) caxv_a1, d.idmar,
  6           SUM (DECODE (b.unxv, NULL, 0, b.unxv * c.coepdt)) unxv_a1,
  7           SUM (DECODE (b.pxv, NULL, 0, b.pxv)) pxv_a1,
  8           SUM (DECODE (b.caxh, NULL, 0, b.caxh)) caxh_a1,
  9           SUM (DECODE (b.unxh, NULL, 0, b.unxh * c.coepdt)) unxh_a1,
 10           SUM (DECODE (b.pxh, NULL, 0, b.pxh)) pxh_a1
 11      FROM zwxp.job_vntxpo_uga a,
 12           zwxp.job_vntxpo_uga b,
 13           (SELECT codpdt, coepdt, idmar, amm
 14              FROM xponent_pdtmar) c,
 15           (SELECT idmar
 16              FROM xponent_mar) d
 17     WHERE a.uga = b.uga(+)
 18       AND a.codcip = b.codcip(+)
 19       AND b.codcip = c.amm(+)
 20       AND c.idmar = d.idmar(+)
 21       AND b.mois(+) = ADD_MONTHS (a.mois, -12)

22 GROUP BY d.idmar, a.mois, a.uga
23

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 65188 | 5538K|
| 710K|
| 1 | SORT GROUP BY | | 65188 | 5538K|
1343M| 710K|
| 2 | NESTED LOOPS OUTER | | 14M| 1206M|
| 303K|
|* 3 | HASH JOIN OUTER | | 14M| 1179M|
860M| 303K|
| 4 | MERGE JOIN OUTER | | 10M| 740M|
| 284K|
| 5 | SORT JOIN | | 10M| 370M|
1204M| 142K|
| 6 | TABLE ACCESS FULL | JOB_VNTXPO_UGA | 10M| 370M|
| 3776 |
|* 7 | SORT JOIN | | 10M| 370M|
1204M| 142K|
| 8 | TABLE ACCESS FULL | JOB_VNTXPO_UGA | 10M| 370M|
| 3776 |
| 9 | INDEX FAST FULL SCAN| XPONENT_PDTMAR_IDX1 | 781 | 8591 |
| 2 |
|* 10 | INDEX UNIQUE SCAN | XPONENT_MAR_PK | 1 | 2 |
| |

Predicate Information (identified by operation id):

   3 - access("B"."CODCIP"="XPONENT_PDTMAR"."AMM"(+))
   7 - access("A"."UGA"="B"."UGA"(+))
       filter("B"."MOIS"(+)=ADD_MONTHS("A"."MOIS",-12) AND 
"A"."CODCIP"="B"."CODCIP"(+)
              AND "A"."UGA"="B"."UGA"(+))

10 - access("XPONENT_PDTMAR"."IDMAR"="XPONENT_MAR"."IDMAR"(+)) Received on Thu Jan 18 2007 - 14:06:32 CST