Discussion :

[ouppsss]

Bonjour,


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

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
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 ?

[Waldar]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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
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;

[pachot]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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 !

[ouppsss]

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à.....

[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)

[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