Plan d'exécution passe de HASH JOIN à NESTED LOOP
Bonjour,
j'ai la problématique suivante :
en schématisant il y a 3 grosses tables T1, T2, T3 (plusieurs millions de lignes) qui sont liées en cascade sur 2 axes de jointures
- 1 hiérarchique avec les ID
- 1 géographique sur la zone, depuis une table ZONE
Les 3 tables sont partitionnées par zone.
De plus les objets présents à chaque niveau ont une durée de validité comprise entre 2 dates de début et de fin.
La table TEMPS qui contient un calendrier permet de générer les jours de l'intervalle
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| ZONE
IDZONE (PK)
LIBZONE
T1
IDZONE
ID1 (PK)
IDZONE
DATE_DEBUT1
DATE_FIN1
T2
IDZONE
ID1 (FK)
ID2 (PK)
DATE_DEBUT2
DATE_FIN2
T3
IDZONE
ID2 (FK)
ID3 (PK)
DATE_DEBUT3
DATE_FIN3
TEMPS
JOUR (PK) |
La requête de base est de retrouver tous les Objets de T1 d'une zone donnée, avec tous les objets associés par l'ID dans T2 puis T3, pour un jour donné, donc comprise dans l'intervalle [DATE_DEBUTx, DATE_FINx]
Ces données doivent être accédées par BO, alors pour éviter des boucles à cause des dates j'ai fait une vue regroupant les clés :
Code:
1
2
3
4
5
6
7
8
9
10
| create view V as
select t.JOUR, z.IDZONE, T1.ID1, T2.ID2, T3.ID3
from ZONE z
join T1 on T1.IDZONE = z.IDZONE
join T2 on T2.IDZONE = T1.IDZONE and T2.ID1=T1.ID1
join T3 on T3.IDZONE = T2.IDZONE and T3.ID2=T2.ID2
join TEMPS t
on t.JOUR between DATE_DEBUT1 and DATE_FIN1
and t.JOUR between DATE_DEBUT2 and DATE_FIN2
and t.JOUR between DATE_DEBUT3 and DATE_FIN3 |
cette vue étant jointe dans l'univers aux 5 tables par les PK.
si l'on applique le critère de zone sur la vue :
Code:
1
2
| select * from V
where LIBZONE="xxx" |
le plan d'exécution idéal donne un HASH JOIN entre ZONE, T1, T2 et T3 sur la seule partition de la zone concernée pour T1,T2,T3 avec un filtre sur ZONE et des JOIN PART FILTER sur les ID entre T1,T2 et T3.
Et pour finir un MERGE JOIN avec TEMPS avec les filtres BETWEEN sur les dates.
Le problème est que si l'on ajoute le critère sur le jour
Code:
1
2
3
| select * from V
where LIBZONE="xxx"
and JOUR=TO_DATE('01/12/2015','DD/MM/YYYY') |
l'optimiseur change complètement le plan et entre par TEMPS puis fait des NESTED LOOP sur les tables T1,T2 et T3, et plus d'exclusion de partition !
du coup les perfs en prennent un coup...
Et ces 2 critères sont obligatoires pour tous les rapports.
J'ai recréé la vue de cette façon pour forcer les HASH JOIN
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| create view V as
with V1 as (
select /*+ USE_HASH(T1) USE_HASH(T2) USE_HASH(T3) */
T1.IDZONE
,T1.ID1
,T1.DATE_DEBUT1
,T1.DATE_FIN1
,T2.ID2
,T2.DATE_DEBUT1
,T2.DATE_FIN1
,T3.ID3
,T3.DATE_DEBUT1
,T3.DATE_FIN1
from T1
join T2 on T2.IDZONE = T1.IDZONE and T2.ID1=T1.ID1
join T3 on T3.IDZONE = T2.IDZONE and T3.ID2=T2.ID2
)
select
t.JOUR, z.IDZONE, T1.ID1, T2.ID2, T3.ID3
from ZONE z
join V1 on V1.IDZONE = z.IDZONE
join TEMPS t
on t.JOUR between DATE_DEBUT1 and DATE_FIN1
and t.JOUR between DATE_DEBUT2 and DATE_FIN2
and t.JOUR between DATE_DEBUT3 and DATE_FIN3 |
mais ça ne change rien, toujours les NESTED LOOP ...
Note : il n'y pas d'index (ce qui empire les choses)
Quelqu'un saurait il si l'on peut empêcher l'optimiseur de rentrer par les dates mais de prendre celles-ci simplement comme filtre ?
Merci !
