> 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
> --------------------------------------------------------------------------- -------------------------

> --------------------------------------------------------------------------- -------------
> | Id  | Operation               |  Name                | Rows  | Bytes
> |TempSpc| Cost  |
> --------------------------------------------------------------------------- -------------
> |   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"(+))