[Débutant] Optimisation de requête ?

[ghohm]

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 :

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]

Que pensez-vous de ça?

Code :

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]

Et de ça ?

Code :

AND (sysdate - CAL.CAL_DAT_FER) < 14

[ghohm]

Envoyé par Bruno2r

Et de ça ?

Code :

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]

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]

Envoyé par Michel SALAIS

Que pensez-vous de ça?

Code :

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]

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]

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]

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]

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 :

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]

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]

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 :

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