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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 !