Don't run just after seeing the Oracle SQL query below ! :)
I put the complete query in ordrer to ask for some advices on optimizations. I used the Oracle explain plan tool to help me identify some ways of optimizations but I'm quite stuck with it. Could you give me some advices/tips/good practices/ on potential optimizations of this query ?
Facts :
- Tables PERSONNE and AFFAIRE contains about 1 million rows
- Table EVENEMENT contains about 30 millions rows
Thanks in advance !
SELECT COUNT(*)
FROM(
(SELECT DISTINCT PER_ID
FROM (
SELECT evt.PER_ID, MAX(evt.EVT_DATE_UPDATE) DMAX
FROM EVENEMENT evt, PERSONNE per
WHERE per.PER_ID = evt.PER_ID
AND (per.PER_SI_DECES <> 1)
GROUP BY evt.PER_ID) dos
WHERE NOT EXISTS (
SELECT 1
FROM AFFAIRE aff, PRISE_EN_CHARGE pec
WHERE dos.PER_ID = aff.PER_ID
AND aff.AFF_ID = pec.AFF_ID
AND pec.PEC_ETAT = 'A')
AND DMAX < ADD_MONTHS(SYSDATE,-60)
)
UNION
(SELECT per.PER_ID
FROM PERSONNE per
WHERE per.PER_SI_DECES = 1
AND NOT EXISTS (
SELECT 1
FROM AFFAIRE aff, PRISE_EN_CHARGE pec
WHERE per.PER_ID = aff.PER_ID
AND aff.AFF_ID = pec.AFF_ID
AND pec.PEC_ETAT = 'A')
AND EXISTS (
SELECT 1
FROM AFFAIRE aff
WHERE per.PER_ID = aff.PER_ID)
)
)
WHERE PER_ID NOT IN (
(SELECT pdo.PER_ID
FROM PERSONNE_DOSSIER pdo, EVENEMENT evt
WHERE pdo.ROP_ID = 1
AND evt.PDO_ID = pdo.PDO_ID
AND evt.PER_ID <> pdo.PER_ID
)
UNION
(SELECT pdo.PER_ID
FROM PERSONNE_DOSSIER pdo, DESTINATAIRE_EVENEMENT des, EVENEMENT evt
WHERE pdo.ROP_ID = 1
AND des.PDO_ID = pdo.PDO_ID
AND des.EVT_ID = evt.EVT_ID
AND evt.PER_ID <> pdo.PER_ID)
UNION
(SELECT dgi.PER_ID
FROM DEMANDE_GIDE dgi, AFFAIRE aff
where aff.AFF_ID = dgi.AFF_ID
and aff.PER_ID <> dgi.PER_ID)
);
Here is the corresponding explain plan :
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | 194K (1)|
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
| 3 | VIEW | | 1309 | 17017 | | 192K (1)|
| 4 | SORT UNIQUE | | 1309 | 35344 | | 192K (24)|
| 5 | UNION-ALL | | | | | |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 1 | 28 | | 149K (1)|
|* 8 | HASH JOIN RIGHT ANTI | | 1021K| 27M| 12M| 149K (1)|
| 9 | VIEW | VW_SQ_1 | 756K| 4433K| | 26286 (1)|
|* 10 | HASH JOIN | | 756K| 14M| 14M| 26286 (1)|
|* 11 | INDEX FAST FULL SCAN | IDX_PEC_AFF_SER_ETAT | 756K| 5910K| | 4080 (3)|
| 12 | TABLE ACCESS FULL | AFFAIRE | 3146K| 36M| | 17884 (1)|
|* 13 | HASH JOIN | | 3648K| 76M| 2560K| 115K (1)|
|* 14 | TABLE ACCESS FULL | PERSONNE | 130K| 1021K| | 14179 (2)|
| 15 | TABLE ACCESS FULL | EVENEMENT | 29M| 391M| | 65035 (2)|
|* 16 | HASH JOIN SEMI | | 1308 | 35316 | | 43080 (1)|
|* 17 | HASH JOIN ANTI | | 1308 | 27468 | 2560K| 41493 (1)|
|* 18 | TABLE ACCESS FULL | PERSONNE | 130K| 1021K| | 14179 (2)|
| 19 | VIEW | VW_SQ_2 | 756K| 9605K| | 26286 (1)|
|* 20 | HASH JOIN | | 756K| 14M| 14M| 26286 (1)|
|* 21 | INDEX FAST FULL SCAN | IDX_PEC_AFF_SER_ETAT | 756K| 5910K| | 4080 (3)|
| 22 | TABLE ACCESS FULL | AFFAIRE | 3146K| 36M| | 17884 (1)|
| 23 | INDEX FAST FULL SCAN | FK_PER_AFF | 3146K| 18M| | 1572 (2)|
| 24 | SORT UNIQUE | | 6 | 171 | | 1757 (100)|
| 25 | UNION-ALL | | | | | |
| 26 | NESTED LOOPS | | | | | |
| 27 | NESTED LOOPS | | 2 | 46 | | 8 (0)|
|* 28 | TABLE ACCESS BY INDEX ROWID | PERSONNE_DOSSIER | 1 | 15 | | 4 (0)|
|* 29 | INDEX RANGE SCAN | FK_PER_PDO | 1 | | | 3 (0)|
|* 30 | INDEX RANGE SCAN | FK_EVT_PDO | 2 | | | 2 (0)|
|* 31 | TABLE ACCESS BY INDEX ROWID | EVENEMENT | 2 | 16 | | 4 (0)|
| 32 | NESTED LOOPS | | | | | |
| 33 | NESTED LOOPS | | 3 | 108 | | 16 (0)|
| 34 | NESTED LOOPS | | 3 | 72 | | 10 (0)|
|* 35 | TABLE ACCESS BY INDEX ROWID| PERSONNE_DOSSIER | 1 | 15 | | 4 (0)|
|* 36 | INDEX RANGE SCAN | FK_PER_PDO | 1 | | | 3 (0)|
| 37 | TABLE ACCESS BY INDEX ROWID| DESTINATAIRE_EVENEMENT | 3 | 27 | | 6 (0)|
|* 38 | INDEX RANGE SCAN | FK_DVT_PDO | 3 | | | 2 (0)|
|* 39 | INDEX UNIQUE SCAN | PK_EVENEMENT | 1 | | | 1 (0)|
|* 40 | TABLE ACCESS BY INDEX ROWID | EVENEMENT | 1 | 12 | | 2 (0)|
| 41 | NESTED LOOPS | | | | | |
| 42 | NESTED LOOPS | | 1 | 17 | | 1730 (2)|
|* 43 | TABLE ACCESS FULL | DEMANDE_GIDE | 1 | 5 | | 1728 (2)|
|* 44 | INDEX UNIQUE SCAN | PK_AFFAIRE | 1 | | | 1 (0)|
|* 45 | TABLE ACCESS BY INDEX ROWID | AFFAIRE | 1 | 12 | | 2 (0)|
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS ( (SELECT "PDO"."PER_ID" FROM "EVENEMENT" "EVT","PERSONNE_DOSSIER"
"PDO" WHERE "PDO"."PER_ID"=:B1 AND "PDO"."ROP_ID"=1 AND "EVT"."PDO_ID"="PDO"."PDO_ID" AND
"EVT"."PER_ID"<>:B2 AND "EVT"."PER_ID"<>"PDO"."PER_ID" AND "EVT"."PDO_ID" IS NOT NULL)UNION
(SELECT "PDO"."PER_ID" FROM "EVENEMENT" "EVT","DESTINATAIRE_EVENEMENT" "DES","PERSONNE_DOSSIER"
"PDO" WHERE "PDO"."PER_ID"=:B3 AND "PDO"."ROP_ID"=1 AND "DES"."PDO_ID"="PDO"."PDO_ID" AND
"DES"."PDO_ID" IS NOT NULL AND "DES"."EVT_ID"="EVT"."EVT_ID" AND "EVT"."PER_ID"<>:B4 AND
"EVT"."PER_ID"<>"PDO"."PER_ID")UNION (SELECT "DGI"."PER_ID" FROM "AFFAIRE" "AFF","DEMANDE_GIDE"
"DGI" WHERE "DGI"."AFF_ID" IS NOT NULL AND "DGI"."PER_ID"=:B5 AND "AFF"."AFF_ID"="DGI"."AFF_ID"
AND "AFF"."PER_ID"<>:B6 AND "AFF"."PER_ID"<>"DGI"."PER_ID")))
6 - filter(MAX("EVT"."EVT_DATE_UPDATE")<ADD_MONTHS(SYSDATE@!,-60))
8 - access("EVT"."PER_ID"="ITEM_1")
10 - access("AFF"."AFF_ID"="PEC"."AFF_ID")
11 - filter("PEC"."PEC_ETAT"='A')
13 - access("PER"."PER_ID"="EVT"."PER_ID")
14 - filter("PER"."PER_SI_DECES"<>1)
16 - access("PER"."PER_ID"="AFF"."PER_ID")
17 - access("PER"."PER_ID"="ITEM_2")
18 - filter("PER"."PER_SI_DECES"=1)
20 - access("AFF"."AFF_ID"="PEC"."AFF_ID")
21 - filter("PEC"."PEC_ETAT"='A')
28 - filter("PDO"."ROP_ID"=1)
29 - access("PDO"."PER_ID"=:B1)
30 - access("EVT"."PDO_ID"="PDO"."PDO_ID")
filter("EVT"."PDO_ID" IS NOT NULL)
31 - filter("EVT"."PER_ID"<>:B1 AND "EVT"."PER_ID"<>"PDO"."PER_ID")
35 - filter("PDO"."ROP_ID"=1)
36 - access("PDO"."PER_ID"=:B1)
38 - access("DES"."PDO_ID"="PDO"."PDO_ID")
filter("DES"."PDO_ID" IS NOT NULL)
39 - access("DES"."EVT_ID"="EVT"."EVT_ID")
40 - filter("EVT"."PER_ID"<>:B1 AND "EVT"."PER_ID"<>"PDO"."PER_ID")
43 - filter("DGI"."AFF_ID" IS NOT NULL AND "DGI"."PER_ID"=:B1)
44 - access("AFF"."AFF_ID"="DGI"."AFF_ID")
45 - filter("AFF"."PER_ID"<>:B1 AND "AFF"."PER_ID"<>"DGI"."PER_ID")
