[Débutant] Optimisation de requête ?

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 ! :mouarf:

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 ?! :oops:

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

---

Et de ça ?

Code:

---

AND (sysdate - CAL.CAL_DAT_FER) < 14

---

Citation:

---

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 ?

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.

Citation:

---

Envoyé par 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) )

---

---

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.

Citation:

---

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 !!! :aie:

Citation:

---

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. :oops:

Citation:

---

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 !

Comme disait pdelorme :

Citation:

---

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

Citation:

---

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

---

Citation:

---

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 :yaisse2:

Juste un index normal ...

Citation:

---

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 :P

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

---