[Débutant] Optimisation de requête ?

**ghohm** · 12/12/2007, 15h26

Bonjour à tous,

j'ai une table contenant des évènements lesquels ayant une date de fermeture.

Il me faut récupérer tous les évènements dont la date de fermeture arrive à échéance la semaine précédent la semaine du jour d'exécution de la requête.

Ceux qui ont compris peuvent lever la main ... les autres relisez ma phrase, car j'ai fait un effort !

Extrait de la requête :

Code :

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

1
2
3
4
5
6
7
8
9
AND (-- condition "normale"
    TO_CHAR(CAL.CAL_DAT_FER, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
    AND TO_CHAR(CAL.CAL_DAT_FER, 'WW') = (TO_CHAR(SYSDATE, 'WW') - 1)
)
OR (-- oui ... 1ère semaine de l'année - 1 = dernière semaine de l'année précédente (= 52) !
    TO_CHAR(CAL.CAL_DAT_FER, 'YYYY') = (TO_CHAR(SYSDATE, 'YYYY') - 1)
    AND TO_CHAR(CAL.CAL_DAT_FER, 'WW') = '52'
    AND TO_CHAR(SYSDATE, 'WW') = '01'
)

Alors qui saura optimiser ma condition ?!

**Michel SALAIS** · 12/12/2007, 22h14

Que pensez-vous de ça?

Code :

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

1
2
3
4
5
6
 
(
    CAL.CAL_DAT_FER >= next_day ( trunc(sysdate) - 14, 1)
AND
    CAL.CAL_DAT_FER < next_day ( trunc(sysdate) - 7, 1)
)

**Bruno2r** · 13/12/2007, 08h23

Et de ça ?

Code :

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

AND (sysdate - CAL.CAL_DAT_FER) < 14

**ghohm** · 13/12/2007, 09h39

Envoyé par Bruno2r

Et de ça ?

Code :

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

AND (sysdate - CAL.CAL_DAT_FER) < 14

Je ne pense pas que cette solution soit viable, puisqu'il faut uniquement que la date de fermeture soit comprise dans la semaine précédent la date d'exécution et non pas le jour précédent par exemple. Ce qui serait le cas lorsque ta condition renverra 1, non ?

**pdelorme** · 13/12/2007, 09h50

Une autre solution est de créer des index.

Attention l'index doit etre crée sur la fonction
TO_CHAR(CAL.CAL_DAT_FER, 'YYYY')

et pas simplement sur la colonne CAL.CAL_DAT_FER

sinon le SGBD (sur Oracle en tous cas) n'est pas capable d'utiliser l'index.

P.

**ghohm** · 13/12/2007, 10h20

Envoyé par Michel SALAIS

Que pensez-vous de ça?

Code :

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

1
2
3
4
5
6
 
(
    CAL.CAL_DAT_FER >= next_day ( trunc(sysdate) - 14, 1)
AND
    CAL.CAL_DAT_FER < next_day ( trunc(sysdate) - 7, 1)
)

Cette solution fonctionne effectivement, mais est moins optimale que ma méthode.

Ta méthode : 280 746 (coût) et 10,5 secondes (en moyenne) pour 31 888 résultats.

Ma méthode : 14 918 (coût) et 8,2 secondes (en moyenne) pour 31 888 résultats.

**ghohm** · 13/12/2007, 10h26

Envoyé par pdelorme

Une autre solution est de créer des index.

J'ai beaucoup d'autres conditions dans ma requête et quasiment toutes sur une et unique table. J'ai donc créé un index sur les champs les plus discriminants. La table étant très lourde je suis passé d'un cout de 1.8 * 10^18 à 14 918 !!!

Envoyé par pdelorme

Attention l'index doit etre crée sur la fonction
TO_CHAR(CAL.CAL_DAT_FER, 'YYYY')

et pas simplement sur la colonne CAL.CAL_DAT_FER

sinon le SGBD (sur Oracle en tous cas) n'est pas capable d'utiliser l'index.

P.

Penses-tu qu'il est vraiment utile de créer cet index supplémentaire et si oui comment ?!

Je n'ai jamais créé d'index intégrant une fonction, uniquement sur des champs.

De plus, j'utilise Oracle SQL Developer et je crée mes index en mode graphique habituellement et non pas en ligne de commande.

**Michel SALAIS** · 13/12/2007, 14h17

Envoyé par ghohm

Cette solution fonctionne effectivement, mais est moins optimale que ma méthode.

Ta méthode : 280 746 (coût) et 10,5 secondes (en moyenne) pour 31 888 résultats.

Ma méthode : 14 918 (coût) et 8,2 secondes (en moyenne) pour 31 888 résultats.

Et si un index est créé sur la colonne ...

Le coût constaté ici par l'optimiseur peut être discutable !

**ghohm** · 13/12/2007, 14h39

Comme disait pdelorme :

Envoyé par pdelorme

Une autre solution est de créer des index.

Attention l'index doit etre crée sur la fonction
TO_CHAR(CAL.CAL_DAT_FER, 'YYYY')

et pas simplement sur la colonne CAL.CAL_DAT_FER

sinon le SGBD (sur Oracle en tous cas) n'est pas capable d'utiliser l'index.

P.

Par contre je ne sais pas comment créer un index sur une colonne à laquelle est appliquée une fonction !?

**Bruno2r** · 13/12/2007, 16h11

Envoyé par ghohm

Je ne pense pas que cette solution soit viable, puisqu'il faut uniquement que la date de fermeture soit comprise dans la semaine précédent la date d'exécution et non pas le jour précédent par exemple. Ce qui serait le cas lorsque ta condition renverra 1, non ?

Exact j'ai voulu aller trop vite ... désolé
Il suffit d'ajouter le test sur la semaine :

Code :

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

1
2
3
4
5
AND (
(sysdate - CAL.CAL_DAT_FER) < 14 
AND TO_CHAR(CAL.CAL_DAT_FER, 'WW') != TO_CHAR(SYSDATE, 'WW')
)

**Michel SALAIS** · 13/12/2007, 16h13

Envoyé par ghohm

Comme disait pdelorme :

Par contre je ne sais pas comment créer un index sur une colonne à laquelle est appliquée une fonction !?

Justement la solution que je te propose ne nécessite pas d'index basé sur une fonction

Juste un index normal ...

**Michel SALAIS** · 13/12/2007, 19h09

Envoyé par ghohm

Cette solution fonctionne effectivement, mais est moins optimale que ma méthode.

Ta méthode : 280 746 (coût) et 10,5 secondes (en moyenne) pour 31 888 résultats.

Ma méthode : 14 918 (coût) et 8,2 secondes (en moyenne) pour 31 888 résultats.

En fait c'est les curiosités de sysdate et le plan d'exécution que j'ai testé ajoute un filtre lorsqu'il s'agit de sysdate!

En fin voici un test pour comparer les deux méthodes sans les "conneries" de sysdate en ce qui concerne ma solution

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
set echo on
set timing on

drop table test purge;
create table test (a date);

insert into test
select rownum / (24 * 60) + sysdate - 1000
from dba_objects, dba_objects
where rownum <= 10000000;

select count(*)
from test
where (-- condition "normale"
    TO_CHAR(a, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
    AND TO_CHAR(a, 'WW') = (TO_CHAR(SYSDATE, 'WW') - 1)
)
OR (-- oui ... 1ère semaine de l'année - 1 = dernière semaine de l'année précédente (= 52) !
    TO_CHAR(a, 'YYYY') = (TO_CHAR(SYSDATE, 'YYYY') - 1)
    AND TO_CHAR(a, 'WW') = '52'
    AND TO_CHAR(SYSDATE, 'WW') = '01'
)

COUNT(*)               
---------------------- 
10080                  

1 rows selected

16,722ms elapsed
declare
  fin date;
  debut date;
  cnt number;
begin
  fin := next_day (trunc(sysdate) - 7, 'lundi');
  debut := next_day (trunc(sysdate) - 14, 'lundi');
  select count(*)
  into cnt
  from test
  where a >= debut
    and a < fin;
end;

anonymous block completed
695ms elapsed

[Débutant] Optimisation de requête ?

SQL Oracle

Discussions similaires

Partager

Partager