Tuning Sql & jointure

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

j'ai un problème avec une jointure sur une table avec PK.

J'ai la requête suivante qui renvoie 100 resultats en 562ms :

Code:

1
2
WITH MYDATA_LVL1_N0 as ( <select> )
 SELECT PRIX,IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2,RN,RN_H FROM MYDATA_LVL2_N0 AA

Sans changer le contenu du "with" , j'ajoute une jointure sur une table AW3_FICHEPASSAGE, contenant 19 millions de rows, sur une PK:

Code:

1
2
3
WITH MYDATA_LVL1_N0 as ( <select> )
 SELECT PRIX,IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2,RN,RN_H,L0.POSITIONSPOT FROM MYDATA_LVL2_N0 AA  
                                                                     LEFT JOIN AW3_FICHEPASSAGE L0 ON L0.IDFICHEPASSAGE = AA.IDFICHEPASSAGE1

La requête dure 25secondes... j'ai l'impression que la PK de AW3_FICHEPASSAGE n'est pas prise en compte.
D'ailleurs si je fait un explain, il me met un <<Node Cost>> à 37448 sur un <<obj name>> AW3_FICHEPASSAGE ( si join copie d'écran )

Voici la requête complète :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 WITH MYDATA_LVL1_N0 as ( SELECT IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2,sum(PRIX) as PRIX
											FROM (SELECT IDFICHEANNONCEUR,AA.IDFICHEPASSAGE as IDFICHEPASSAGE1,AA.IDFICHEPASSAGE as IDFICHEPASSAGE2,sum(PRIX) as PRIX
								FROM AW3_AGG_3_2010_3  AA
									 WHERE (AA.IDCLASSEMEDIA in ( 3,142) ) AND  ( DATEACTIVITE between to_date('2010-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2010-04-30 23:59:59','YYYY-MM-DD HH24:MI:SS') ) 
									GROUP BY IDFICHEANNONCEUR,AA.IDFICHEPASSAGE
 UNION ALL 
SELECT IDFICHEANNONCEUR,AA.IDFICHEPASSAGE as IDFICHEPASSAGE1,AA.IDFICHEPASSAGE as IDFICHEPASSAGE2,sum(PRIX) as PRIX
								FROM AW3_AGG_3_2010_142  AA
									 WHERE (AA.IDCLASSEMEDIA in ( 3,142) ) AND  ( DATEACTIVITE between to_date('2010-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2010-04-30 23:59:59','YYYY-MM-DD HH24:MI:SS') ) 
									GROUP BY IDFICHEANNONCEUR,AA.IDFICHEPASSAGE) AA
												GROUP BY IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2),
MYDATA_LVL2_N0 as ( SELECt * FROM (
											SELECT AA.*,
													dense_rank() OVER(ORDER BY PART_SCORE DESC,IDFICHEPASSAGE1,IDFICHEPASSAGE2) AS RN  ,
													1 as RN_H
													FROM ( SELECT AA.*, 0 as PART_SCORE FROM MYDATA_LVL1_N0 AA  ) AA
											)
										WHERE RN > 0 AND RN <= 100 AND RN_H <= 100 
										)
 SELECT PRIX,IDFICHEANNONCEUR,AA.IDFICHEPASSAGE1,AA.IDFICHEPASSAGE2,RN,RN_H,L0.POSITIONSPOT FROM MYDATA_LVL2_N0 AA  
                                                                     LEFT JOIN AW3_FICHEPASSAGE L0 ON L0.IDFICHEPASSAGE = AA.IDFICHEPASSAGE1

Pouvez-vous m'aider ?
Merci !
Stéphane

Bref, il a un bon plan parce qu’il n’a pas compris que vous voulez seulement 100 enregistrements, regardez les cardinalités. Vous pouvez utiliser une requête scalaire pour récupérer LO.POSITIONPOT pour y remédier (vite fait). Mais très probablement que si vous écrivez toute la requête sans en mélanger trop la partie WITH avec la partie "inline vue" les choses vont aller d’elles mêmes :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 With Stage1 ( Select col, ... from table 1 Union Select col, ... From table 2 ), Stage 2 As ( Select col1, sum(col2) From Stage1 ), Stage 3 ( Select col1, col2, dense_rank From Stage2 ) Stage 4 As ( Select ... From Stage 3 Where RN ... ) Select * From Stage 4

Il y a pas mal d'incohérences dans votre requête.

Rien dans votre requête ne justifie de dédoubler la colonne idfichepassage.
Et quand bien même, il vaut mieux le faire le plus tard possible, sinon c'est de la mémoire occupée inutilement.

Vous avez une sous-requête où vous créez une colonne part_score qui prend un constante 0, puis vous faites un dense_rank en triant sur celle-ci.
Ça n'a aucun intérêt.
Même remarque pour RN_H, qui vaut 1, mais plus loin vous précisez de prendre les RN_H inférieur à 100.

De même vous utilisez la fonction dense_rank(), et plus loin vous demandez à ce qu'elle soit supérieure à 0, ce qui est encore une fois inutile.

Enfin, vous faites une jointure externe sur l'autre table, sans aucun filtre, donc à priori aucune raison d'utiliser l'index de la clef primaire.
Edit : mnitu à vu juste je pense sur ce problème, l'optimiseur a perdu la notion des 100 lignes à cause des moultes imbrications.
D'ailleurs row_number() me paraît plus adapté dans ce cas-là.

Au final, j'aurai écrit votre requête de cette manière :
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 With AllAgg as ( select idficheannonceur, idfichepassage, idclassemedia, dateactivite, prix from aw3_agg_3_2010_3 union all select idficheannonceur, idfichepassage, idclassemedia, dateactivite, prix from aw3_agg_3_2010_142 ) , MyData AS ( select idficheannonceur, idfichepassage, sum(prix) as prix, row_number() over(order by idfichepassage asc) as rn from AllAgg where idclassemedia in (3, 142) and dateactivite between to_date('2010-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2010-04-30 23:59:59','YYYY-MM-DD HH24:MI:SS') group by idficheannonceur, idfichepassage ) select aa.prix, aa.idficheannonceur, aa.idfichepassage, aa.rn, 1 as rn_h, l0.positionspot from mydata aa left outer join aw3_fichepassage l0 on l0.idfichepassage = aa.idfichepassage1 where aa.rn <= 100;

11/05/2010, 15h59
Stef784ever

Merci pour vous réponse .

Waldar :

En fait, j'essaie de générer la requête plus ou moins dynamiquement à partir de ce que demande l'utilisateur (en PHP).

Ceci explique les petits trucs inutiles ( le part_score, le RN_H ), je vais essayé d'optimiser dans un second temps.

A vous deux :
Vous avez raison, il suffit d'ajouter ROWNUM < 1000 et il passe par la PK :)

Merci beaucoup !