Hi,
This code is taking too much time to complete :
WITH rawData AS -- 563 rows in 0.07s OR 59 rows in 0.02s
(
SELECT
date_releve AS x1,
index_corrige AS y1,
LEAD(date_releve) OVER (PARTITION BY id_compteur ORDER BY date_releve) AS x2,
LEAD(index_corrige) OVER (PARTITION BY id_compteur ORDER BY date_releve) AS y2,
id AS id_releve,
id_compteur
FROM V_relevesCorriges
),
meteoData AS -- 1082 rows in 1.34s OR 116 rows in 0.16s
(
SELECT avg(meteo.valeur) AS meteoValue, x2 AS dateMeteo, id_variable, id_releve, id_compteur
FROM meteo, rawData
WHERE date_meteo <= x2 AND date_meteo > x1
GROUP BY id_releve, id_variable, x2, id_compteur
ORDER BY x2
),
consoData AS -- 1104 rows in 1.43s, 117 rows in 0.2s
(
SELECT
to_char(x1, 'DD.MM.YYYY') || ' - ' || to_char(x2, 'DD.MM.YYYY') AS periode,
meteoValue AS meteo_moyenne,
(y2 - y1) / nullif((x2 - x1),0) AS conso_par_jour,
(y2 - y1) AS conso,
rawData.id_releve id_releve,
meteoData.id_variable id_variable,
meteoData.id_compteur id_compteur
FROM rawData LEFT OUTER JOIN meteoData ON rawData.id_releve = meteoData.id_releve
ORDER BY x2
)
SELECT periode, meteo_moyenne, conso_par_jour, consoData.id_variable id_variable, consoData.id_releve id_releve, id_compteur -- 1104 rows in 1.51s, 116 rows in 1.34s
FROM consoData LEFT OUTER JOIN diagnostic2 ON consoData.id_releve = diagnostic2.id_releve AND consoData.id_variable = diagnostic2.id_variable
WHERE Id_compteur = 4
If I remove "WHERE Id_compteur = 4" on the last line, it makes almost no difference in execution time. Without this WHERE clause, it returns 1104 rows in 1.51s, with it it returns 116 rows in 1.34s.
I'm saying that it takes too long because when I move this WHERE clause into "consoData" (WHERE meteoData.consoData=4), it returns 116rows in 0.2s, for getting the same data output. If I remove the LEFT OUTER JOIN diagnostic2 (last but one line) it also takes 0.2s.
I think that the solution would be to force "WHERE Id_compteur=..." to take effect before it joins the "diagnostic2" table, but don't know how to do it.
The subquery that takes much time when returning all rows is "meteoData".
This code is meant to be a VIEW, so "WHERE Id_compteur=..." will not be included in it but passed when querying the view. I tested it as a VIEW, same problem.
Explain plan :
Plan hash value: 724835998
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16364 | 1342K| | 586 (4)| 00:00:08 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | DIAGNOSTIC2 | | | | | |
| 3 | WINDOW SORT | | 563 | 15764 | | 12 (25)| 00:00:01 |
| 4 | VIEW | V_RELEVESCORRIGES | 563 | 15764 | | 11 (19)| 00:00:01 |
| 5 | SORT GROUP BY | | 563 | 56300 | | 11 (19)| 00:00:01 |
| 6 | VIEW | | 563 | 56300 | | 10 (10)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 563 | 25335 | | 10 (10)| 00:00:01 |
| 8 | TABLE ACCESS FULL | COMPTEURS | 22 | 132 | | 3 (0)| 00:00:01 |
| 9 | VIEW | | 563 | 21957 | | 7 (15)| 00:00:01 |
|* 10 | HASH JOIN OUTER | | 563 | 26461 | | 7 (15)| 00:00:01 |
| 11 | TABLE ACCESS FULL | RELEVES | 563 | 12949 | | 3 (0)| 00:00:01 |
| 12 | VIEW | V_CORRECTIONDATA | 563 | 13512 | | 3 (0)| 00:00:01 |
|* 13 | VIEW | | 563 | 28150 | | 3 (0)| 00:00:01 |
| 14 | WINDOW SORT | | 563 | 67560 | | 3 (0)| 00:00:01 |
| 15 | VIEW | | 563 | 67560 | | 3 (0)| 00:00:01 |
| 16 | NESTED LOOPS OUTER| | 563 | 14638 | | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL| RELEVES | 563 | 12949 | | 3 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN| COMPTEURS_PK | 1 | 3 | | 0 (0)| 00:00:01 |
|* 19 | HASH JOIN RIGHT OUTER | | 16364 | 1342K| | 573 (4)| 00:00:07 |
| 20 | INDEX FULL SCAN | DIAGNOSTIC2_PK | 4 | 24 | | 1 (0)| 00:00:01 |
| 21 | VIEW | | 16364 | 1246K| | 572 (4)| 00:00:07 |
| 22 | SORT ORDER BY | | 16364 | 1661K| 3864K| 572 (4)| 00:00:07 |
|* 23 | HASH JOIN | | 16364 | 1661K| | 179 (9)| 00:00:03 |
| 24 | VIEW | | 1157 | 55536 | | 3 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6657_90A96D1D | 1157 | 55536 | | 3 (0)| 00:00:01 |
|* 26 | VIEW | | 7963 | 435K| | 175 (8)| 00:00:03 |
| 27 | SORT GROUP BY | | 7963 | 311K| 1768K| 175 (8)| 00:00:03 |
| 28 | MERGE JOIN | | 26409 | 1031K| | 23 (48)| 00:00:01 |
| 29 | SORT JOIN | | 1157 | 28925 | | 4 (25)| 00:00:01 |
| 30 | VIEW | | 1157 | 28925 | | 3 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6657_90A96D1D | 1157 | 55536 | | 3 (0)| 00:00:01 |
|* 32 | FILTER | | | | | | |
|* 33 | SORT JOIN | | 9130 | 133K| | 11 (19)| 00:00:01 |
| 34 | TABLE ACCESS FULL | METEO | 9130 | 133K| | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("RELEVES"."ID_COMPTEUR"="COMPTEURS"."ID"(+))
10 - access("RELEVES"."ID_COMPTEUR"="V_CORRECTIONDATA"."ID_COMPTEUR"(+))
filter("RELEVES"."DATE_RELEVE">="V_CORRECTIONDATA"."DATE_CHANGEMENT"(+))
13 - filter("CHG_COMPTEUR"=1 AND "ID_COMPTEUR"="ID_COMPTEUR_CORR")
18 - access("RELEVES"."ID_COMPTEUR"="COMPTEURS"."ID"(+))
19 - access("CONSODATA"."ID_VARIABLE"="DIAGNOSTIC2"."ID_VARIABLE"(+) AND
"CONSODATA"."ID_RELEVE"="DIAGNOSTIC2"."ID_RELEVE"(+))
23 - access("RAWDATA"."ID_RELEVE"="METEODATA"."ID_RELEVE")
26 - filter("METEODATA"."ID_COMPTEUR"=4)
32 - filter("DATE_METEO">"X1")
33 - access(INTERNAL_FUNCTION("DATE_METEO")<=INTERNAL_FUNCTION("X2"))
filter(INTERNAL_FUNCTION("DATE_METEO")<=INTERNAL_FUNCTION("X2"))
Oracle database version : 10.2.0.4.0, I'm accessing it through APEX version 4.1.1.00.23
I hope that my question is not too fuzzy..