Optimisation requête SQL

Version imprimable

Bonjour,

j'ai 'hérité' d'une requête de la forme suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT PK-CH3, PK-CH2, champ1, champ2, PK-CH5, champ3
  FROM MaTable table1
 WHERE table1.PK-CH7 = 'ABC'
   and table1.PK-CH4 = 'A'
   and table1.PK-CH6 = 'ABC'
   and table1.PK-CH1 = (select max(PK-CH1)
                          from MaTable
                         where PK-CH3 = table1.PK-CH3
                           and PK-CH2 = table1.PK-CH2
                           and PK-CH7 = table1.PK-CH7
                           and PK-CH5 = table1.PK-CH5
                           and PK-CH6 = table1.PK-CH6
                           and PK-CH4 = table1.PK-CH4)
   and table1.PK-CH5 = to_date(to_char(table1.PK-CH5, 'MM/YYYY'), 'MM/YYYY')
   and table1.PK-CH5 between i_date_deb and i_date_fin;

avec

PK-CHx des éléments de la PK (qui est composée des champs PK-CH1 à 7) de la table [TABLE]
PK-CH1 et PK-CH5 sont des dates, PK-CH2 un number et le reste du varchar2
champx des données quelconques de la table [TABLE]

La volumétrie est très importante (une grosse dizaine de millions de lignes)...

Que conseilleriez-vous pour optimiser cette 'charmante' requête et diminuer grandement son temps d'exécution ?

Déjà, quel est le plan d'exécution réel et la durée de la requête ?

Est-ce que l'utilité de cette restriction est prouvée :

Code:

table1.PK-CH5 = to_date(to_char(table1.PK-CH5, 'MM/YYYY'), 'MM/YYYY')

Ça filtre les données qui portent une information horaire autre que minuit pile, mais c'est étrangement écrit.

Peut-être que la jointure est plus efficace que le IN dans ce cas :
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 28 29 30 31 32 With MaTableMax as ( select max(PK-CH1) as PK-CH1 , PK-CH3 , PK-CH2 , PK-CH7 , PK-CH5 , PK-CH6 , PK-CH4 from MaTable group by PK-CH3 , PK-CH2 , PK-CH7 , PK-CH5 , PK-CH6 , PK-CH4 ) SELECT t1.PK-CH3, t1.PK-CH2, t1.champ1, t1.champ2, t1.PK-CH5, t1.champ3 FROM MaTable t1 JOIN MaTableMax t2 ON t2.PK-CH3 = t1.PK-CH3 and t2.PK-CH2 = t1.PK-CH2 and t2.PK-CH7 = t1.PK-CH7 and t2.PK-CH5 = t1.PK-CH5 and t2.PK-CH6 = t1.PK-CH6 and t2.PK-CH4 = t1.PK-CH4 and t2.PK-CH1 = t1.PK-CH1 WHERE t1.PK-CH7 = 'ABC' and t1.PK-CH4 = 'A' and t1.PK-CH6 = 'ABC' and t1.PK-CH5 = trunc(t1.PK-CH5, 'MM') and t1.PK-CH5 between i_date_deb and i_date_fin;

Bonjour,

On doit pouvoir éviter de lire 2x la même table avec une fonction analytique:

Code:

1
2
3
4
5
6
7
8
9
select PK-CH3, PK-CH2, champ1, champ2, PK-CH5, champ3 from (
SELECT PK-CH3, PK-CH2, champ1, champ2, PK-CH5, champ3,max(PK-CH1)over(partition by PK-CH2,PK-CH3,PK-CH4,PK-CH5,PK-CH6,PK-CH7) max_CH1
  FROM MaTable table1
 WHERE table1.PK-CH7 = 'ABC'
   AND table1.PK-CH4 = 'A'
   AND table1.PK-CH6 = 'ABC'
   AND table1.PK-CH5 = to_date(to_char(table1.PK-CH5, 'MM/YYYY'), 'MM/YYYY')
   AND table1.PK-CH5 BETWEEN i_date_deb AND i_date_fin
) where PK_CH1=max_CH1;

(à vérifier que c'est le résultat voulu, c'es tpour donner l'idée)

Cordialement,
Franck.

19/11/2013, 09h27
pacmann

Salut,

@Waldar, j'aurais plutôt dit trunc(t1.PK-CH5, 'MM')

@Pachot, il y a aussi dense_rank() over(partition BY PK-CH2,PK-CH3,PK-CH4,PK-CH5,PK-CH6,PK-CH7 order by PK-CH1 desc) rk + filtre sur rk = 1... mais ça revient au même :)

@ouppsss, en plus du plan d'exécution, les index présents sur la table, ainsi que la sélectivité des colonnes filtrées pourraient aider !
19/11/2013, 11h04
ouppsss

1 pièce(s) jointe(s)
alors dans le désordre :
- le plan d'execution en PJ
- la durée : entre 4h et une journée selon la volumétrie ...
- les index suivront, je n'ai pas accès à la base pour le moment :)
- pour l'étrangeté du filtre je suis d'accord, je me pose les mêmes questions, mais qd tu hérites, et que la personne n'est plus là.....
19/11/2013, 11h17
ouppsss

Les index et PK sur la table [table]:
la PK

alter table [table] add constraint pk_[Table] primary key (PK-CH1,PK-CH2,PK-CH3,PK-CH4,PK-CH5,PK-CH6,PK-CH7)

create index idx_[table] on [table] (PK-CH3, PK-CH2, PK-CH7, PK-CH1)
22/11/2013, 15h01
ouppsss

bonjour,

j'ai effectué des tests avec une requête inspirée de celle suggérée par pachot
cela semble prometteur !

Merci à vous tous :)