Hi,
The following query takes more than 3 hours to execute (10.2.0.4)
any help to optimize this ??
Thanks for your help
SELECT DISTINCT
MAX (NVL (A.DB_SOURCE, 'SIGNIFY')) OVER (PARTITION BY C.COUNTRY)
DB_SOURCE,
A.SUBJECTNUMBERSTR,
A.SUBJECTID,
MAX (A.SITECOUNTRY) OVER (PARTITION BY C.COUNTRY) SITECOUNTRY,
C.COUNTRY,
C.CENTRE,
C.COUNTRY || '-' || C.CENTRE AS SITEMNEMONIC,
A.VISITMNEMONIC,
A.VISITID,
A.FROZENSTATE,
A.SIGNEDSTATE,
A.INCLUS,
A.VISDATRECTHEO,
A.VISIT,
A.VISIT_THEO,
B.DOV,
MAX (C.STUDY_INCL) OVER () STUDY_INCL,
MAX (C.COUNTRY_INCL) OVER (PARTITION BY C.COUNTRY) COUNTRY_INCL,
MAX (C.CENTRE_INCL) OVER (PARTITION BY C.COUNTRY, C.CENTRE)
CENTRE_INCL,
MAX (NVL (C.NB_VISIT_ATTENDUES_GLOBAL, 0)) OVER ()
NB_VISIT_ATTENDUES_GLOBAL,
MAX (NVL (C.NB_VISIT_ATTENDUES_COUNTRY, 0))
OVER (PARTITION BY C.COUNTRY)
NB_VISIT_ATTENDUES_COUNTRY,
MAX (NVL (C.NB_VISIT_ATTENDUES_CENTRE, 0))
OVER (PARTITION BY C.COUNTRY, C.CENTRE)
NB_VISIT_ATTENDUES_CENTRE,
MAX (D.NB_VISIT_CLEAN_GLOBAL) OVER () NB_VISIT_CLEAN_GLOBAL,
MAX (D.NB_VISIT_CLEAN_COUNTRY) OVER (PARTITION BY C.COUNTRY)
NB_VISIT_CLEAN_COUNTRY,
MAX (D.NB_VISIT_CLEAN_COUNTRY)
OVER (PARTITION BY C.COUNTRY, C.CENTRE)
NB_VISIT_CLEAN_CENTRE,
MAX (D.NB_VISIT_CLEAN_INCLUS_GLOBAL) OVER ()
NB_VISIT_CLEAN_INCLUS_GLOBAL,
MAX (D.NB_VISIT_CLEAN_INCLUS_COUNTRY) OVER (PARTITION BY C.COUNTRY)
NB_VISIT_CLEAN_INCLUS_COUNTRY,
MAX (D.NB_VISIT_CLEAN_INCLUS_COUNTRY)
OVER (PARTITION BY C.COUNTRY, C.CENTRE)
NB_VISIT_CLEAN_INCLUS_CENTRE
FROM CL316257083_ECRF_DW.T_BILANS_MENSUELS_TMP A,
CL316257083_ECRF_DW.T_PASTA_UNION B,
CL316257083_ECRF_DW.T_BILAN_SETHI C,
CL316257083_ECRF_DW.T_BILAN_QUERIES D
WHERE ( A.DB_SOURCE = B.DB_SOURCE(+)
AND A.SUBJECTID = B.SUBJECTID(+)
AND A.VISITID = B.VISITID(+))
AND (C.COUNTRY = A.COUNTRY(+) AND C.CENTRE = A.CENTRE(+))
AND (D.COUNTRY(+) = A.COUNTRY AND D.CENTRE(+) = A.CENTRE);
Plan hash value: 3745247003
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10025 | 1556K| | 1544 (13)| 00:00:02 |
| 1 | HASH UNIQUE | | 10025 | 1556K| 3496K| 1544 (13)| 00:00:02 |
| 2 | WINDOW SORT | | 10025 | 1556K| 3496K| 1544 (13)| 00:00:02 |
|* 3 | HASH JOIN OUTER | | 10025 | 1556K| | 915 (20)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 5543 | 703K| | 720 (20)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 5543 | 552K| | 359 (21)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T_BILAN_SETHI | 1277 | 43418 | | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| T_BILANS_MENSUELS_TMP | 259K| 16M| | 342 (18)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T_PASTA_UNION | 105K| 2884K| | 355 (19)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T_BILAN_QUERIES | 107K| 3057K| | 189 (16)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."COUNTRY"(+)="A"."COUNTRY" AND "D"."CENTRE"(+)="A"."CENTRE")
4 - access("A"."DB_SOURCE"="B"."DB_SOURCE"(+) AND "A"."SUBJECTID"="B"."SUBJECTID"(+) AND
"A"."VISITID"="B"."VISITID"(+))
5 - access("C"."COUNTRY"="A"."COUNTRY"(+) AND "C"."CENTRE"="A"."CENTRE"(+))
24 rows selected.
Elapsed: 00:00:11.15