oracle fait ce qu'il veut

Version imprimable

Bonjour,

J'ai la requête suivante qui génère une erreur
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 select NUDOSS, SOCDOS, PGPDOS, 1 NULIGN, TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD'), CODE5C, IDCONT, VALE31, FINP31 , TO_CHAR ( (TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD')), 'DY' ) JRCPTR from ZY5C where substr(anmois,5,2) in ('01', '03', '05', '07', '08', '10', '12') and TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) >= (SYSDATE - 90) and TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) <= (SYSDATE + 90) /
L'erreur générée est ORA-01839: date not valid for month specified.
Le champ anmois de zy5c contient une chaine de type 201001, 201002 etc ..

En revanche la requête suivante passe sans problème :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 select NUDOSS, SOCDOS, PGPDOS, 1 NULIGN, TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD'), CODE5C, IDCONT, VALE31, FINP31 , TO_CHAR ( (TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD')), 'DY' ) JRCPTR from ZY5C where substr(anmois,5,2) in ('01', '03', '05', '07', '08', '10') and TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) >= (SYSDATE - 90) and TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) <= (SYSDATE + 90) /
Il semblerait qu'oracle utilise les filtres dans un ordre qu'il juge plus efficient ce qui fait qu'il évalue le filtre ci-dessous en premier (ce qui gènère une erreur lorsqu'il tombe sur un enregistrement du type 201002 pour anmois (si oracle utilisait les filtres dans l'ordre indiqué dans la requête il n'y aurait pas de problème).
Code:

1 2 3 4 5 6 TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) >= (SYSDATE - 90)
Il y a t-il moyen de forcer l'ordre d'utilisation des filtres ?

merci pour votre aide

Heureusement

Utilisez les fonctions d'Oracle et assurez-vous que les données sont cohérentes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> Select Last_Day(to_date(201002,'YYYYMM')) from dual
  2  /
 
LAST_DAY(TO_DATE(201002,'YYYYM
------------------------------
28/02/2010
 
SQL>

05/10/2010, 16h45
mongolic

merci pour la fonction

merci pour la fonction :).

Mais elle ne répond pas tout à fait à ma question. J'ai réécris la requête et j'ai contourné le problème. Ce n'est pas le soucis.

Mais mon premier réflexe était d'utiliser un hint forçant les filtres à s'exécuter dans un ordre et je n'en ai pas trouvé. Il en existe t-il ?
05/10/2010, 16h52
Waldar

Attention, vous prenez le problème à l'envers.

Vous pensez que votre problème est l'ordre d'évaluation des critères et cherchez à arranger ceci.

Mais votre problème est que votre requête est (était) mal écrite, pas besoin d'aller modifier les process d'Oracle avec une bonne requête.

D'ailleurs n'hésitez pas à poster ce que vous avez réécrit, ça servira toujours pour d'autres qui pourront lire ce sujet.
05/10/2010, 17h15
mnitu

Citation:

Envoyé par mongolic

...
Mais mon premier réflexe était d'utiliser un hint forçant les filtres à s'exécuter dans un ordre et je n'en ai pas trouvé. Il en existe t-il ?

Pour l'instant ignorez les Hints! Vous allez mieux progresser sans.

pas d'accord

Ok la requête aurait pu être mieux écrite. Mais pour moi une requête est soit valide soit invalide. Or là cela dépend des choix de l'optimiseur.

Je vous illustre ma démarche :

J'exécute la requête suivante :
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 select NUDOSS,SOCDOS,PGPDOS,1 NULIGN, TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD'), CODE5C,IDCONT,VALE31,FINP31 , TO_CHAR ( (TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD')), 'DY' ) JRCPTR from ZY5C where substr(anmois,5,2) in ('01', '03', '05', '07', '08', '10', '12') and TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) >= (SYSDATE - 90) and TO_DATE ( (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD' ) <= (SYSDATE + 90)
Cette requête me renvoit des lignes. Et voici le plan associé :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Execution Plan ---------------------------------------------------------- Plan hash value: 940779351 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 5 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| ZY5C | 1 | 31 | 5 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SYSDATE@!-90<=SYSDATE@!+90) 2 - filter((SUBSTR("ANMOIS",5,2)='01' OR SUBSTR("ANMOIS",5,2)='03' OR SUBSTR("ANMOIS",5,2)='05' OR SUBSTR("ANMOIS",5,2)='07' OR SUBSTR("ANMOIS",5,2)='08' OR SUBSTR("ANMOIS",5,2)='10' OR SUBSTR("ANMOIS",5,2)='12') AND TO_DATE(SUBSTR("ANMOIS",1,4)||'-'||SUB STR("ANMOIS",5,2)||'-'||'31','YYYY-MM-DD')>=SYSDATE@!-90 AND TO_DATE(SUBSTR("ANMOIS",1,4)||'-'||SUBSTR("ANMOIS",5,2)||'-'||'31','YYYY -MM-DD')<=SYSDATE@!+90)
On observe que le filtre 2 s'exécute d'abord sur les mois à prendre en compte. De ce fait les mois qui ne font pas 31 jours ne sont pas traités par les filtres suivants et dans ce cas çà fonctionne.
Mes tests sont concluants, supposons que je décide alors de mettre en production la requête.
Et quelques mois après cette même requête ne passe pas (ORA-01839: date not valid for month specified
) et le plan devient :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 5 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| ZY5C | 1 | 31 | 5 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------- 1 - filter(SYSDATE@!-90<=SYSDATE@!+90) 2 - filter(TO_DATE(SUBSTR("ANMOIS",1,4)||'-'||SUBSTR("ANMOIS",5,2)||' -'||'31','YYYY-MM-DD')>=SYSDATE@!-90 AND TO_DATE(SUBSTR("ANMOIS",1,4)||'-'||SUBSTR("ANMOIS",5,2)||'-'||'31','YYYY -MM-DD')<=SYSDATE@!+90 AND (SUBSTR("ANMOIS",5,2)='01' OR SUBSTR("ANMOIS",5,2)='03' OR SUBSTR("ANMOIS",5,2)='05' OR SUBSTR("ANMOIS",5,2)='07' OR SUBSTR("ANMOIS",5,2)='08' OR SUBSTR("ANMOIS",5,2)='10' OR SUBSTR("ANMOIS",5,2)='12'))
L'ordre des filtes a été changé : il essaye d'évaluer d'abord l'expression TO_DATE(SUBSTR("ANMOIS",1,4)||'-'||SUBSTR("ANMOIS",5,2)||'
-'||'31','YYYY-MM-DD')>=SYSDATE@!-90
Ce qui ne passe pas puisque tous les mois sont traités car le filtre sur les mois n'est exécuter que dans un second temps.

De ce fait en fonction des choix de l'optimiseur, la requête est valide ou invalide d'oû le titre initial 'oracle fait ce qu'il veut'. Je trouve çà déconcertant car on peut tester une requête et tout se passe bien, et à un moment donné l'optimiseur change de plan d'exécution et la requête devient in valide sans que la requête n'ai changé en quoique ce soit.

La requête ci-dessus est un extrait d'une requête de 1000 lignes utilisée par une vue qu'on m'a demandé d'installer sur un environnement. Quand j'ai détecté le problème, le client m'a dit "ce n'est pas possible, on l'a déjà fait tourner chez d'autres comptes sans souci". De coup c'est une bombe à retardement car le jour ou l'optimiseur change de plan , paf ! çà plante !

comment j'ai solutionné

J'ai solutionné ce problème en forçant l'exécution du filtre qui m'interessait en l'intégrant dans une table temporaire "matérialisée" :

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
 
with temp31 as 
(select /*+ materialize */ 
NUDOSS,SOCDOS,PGPDOS,1 NULIGN,anmois,CODE5C,IDCONT,VALE31,FINP31 
from ZY5C
where substr(anmois,5,2) in ('01','03','05','07','08','10','12'))
select
NUDOSS,SOCDOS,PGPDOS,1 NULIGN,
TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD'),
CODE5C,IDCONT,VALE31,FINP31 ,
TO_CHAR
(
   (TO_DATE(substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31', 'YYYY-MM-DD')),
   'DY'
)
JRCPTR
from temp31
where TO_DATE
(
   (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD'
)
>= (SYSDATE - 90)
and TO_DATE
(
   (substr(anmois,1,4) || '-' || substr(anmois,5,2) || '-' || '31'), 'YYYY-MM-DD'
)
<= (SYSDATE + 90)
/

06/10/2010, 20h43
mnitu

Citation:

Mais pour moi une requête est soit valide soit invalide

Une requête qui dépende de l'ordre d'évaluation des prédicats n'est pas valide.

Order of predicate evaluation in queries
07/10/2010, 09h35
Garuda

Citation:

http://www.developpez.net/forums/images/icons/icon1.gif oracle fait ce qu'il veut

Le petit coquin !;)

Je suis désolé mais votre problème n'est pas résolu.
Vous devez comprendre comment fonctionnent les dates dans une base de données et utilisez les fonctions qui leur sont dédiées.

En utilisant la fonction proposée par mnitu et en supposant que la colonne "anmois" est bien un CHAR(6) votre requête doit s'écrire ainsi :
Code:

1 2 3 4 5 6 7 select nudoss, socdos, pgpdos, 1 nulign, last_day(to_date(anmois, 'yyyymm')) as dtcptr, code5c, idcont, vale31, finp31 , to_char(last_day(to_date(anmois, 'yyyymm')), 'DY') as jrcptr from zy5c where substr(anmois,5,2) in ('01', '03', '05', '07', '08', '10', '12') and last_day(to_date(anmois, 'yyyymm')) between sysdate - 90 and sysdate + 90;
Accessoirement je suppose que vous avez une requête similaire pour les mois de 30 jours plus une autre pour le mois de février ?
Celle ci-dessus fonctionnera quelque soit votre mois de référence, donc le IN est très certainement supprimable.

07/10/2010, 17h43
mnitu
Citation:
Envoyé par mongolic

J'ai solutionné ce problème en forçant l'exécution du filtre qui m'interessait en l'intégrant dans une table temporaire "matérialisée" :

Code:

1 2 3 4 with temp31 as (select /*+ materialize */ ...
Si vous regardez dans la documentation d'Oracle pour la 11g le Hint Materialize n'existe même pas.
Dans des versions antérieurs vous aurez pu utiliser le Hint ORDERED_PREDICATES. Mais ce hint n'existe plus.

Comme j'ai disais oubliez-les. Ils ajoutent autant des problèmes que vous pensez à résoudre.
07/10/2010, 19h11
mongolic

il vaut mieux que je décrive le but de la requête

En fait ce que l'utilisateur souhaite c'est que pour une valeur anmois donnée : 201009 par exemple, la requête génère tous les jours du mois considérés :
01-01-2010 jusqu'au 31-10-2010 et ceux uniquement pour les jours plus récent que date du jour - 90 jours.
L'exemple ci-dessus correspondait à la génération des 31 pour les mois valides.
Evidemment, générer les 28 premiers jours ne posent aucun problème, c'est après que çà se gate.
mnitu et waldar, votre solution me paraît bien mais faut que j'aille plus loin:
j'utilise last_day last_day-1 last_day-2 et pour les 28 premiers jours je prend la requête d'origine et avec un distinct, je devrais supprimer les éventuels doublons.

merci pour votre aide:ccool:

Le mieux serait d'avoir un calendrier dans votre modèle, vous pouvez ainsi faire des jointures externes avec votre table.

Vous pouvez toujours en générer un dynamiquement au moment de l'exécution :

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
With Parametre as
(
select '201002' as anmois from dual
)
  ,  Calendrier as
(
select to_date(anmois, 'yyyymm') + level - 1 as dt
  from Parametre
connect by level - 1 <= last_day(to_date(anmois, 'yyyymm')) - to_date(anmois, 'yyyymm')
)
select to_char(dt, 'dd/mm/yyyy') as dt
  from Calendrier;
 
        DT
----------
01/02/2010
02/02/2010
03/02/2010
04/02/2010
05/02/2010
06/02/2010
07/02/2010
08/02/2010
09/02/2010
10/02/2010
11/02/2010
12/02/2010
13/02/2010
14/02/2010
15/02/2010
16/02/2010
17/02/2010
18/02/2010
19/02/2010
20/02/2010
21/02/2010
22/02/2010
23/02/2010
24/02/2010
25/02/2010
26/02/2010
27/02/2010
28/02/2010

12/10/2010, 09h29
mongolic

connect by level

Merci pour le dernier post Waldar, le connect by ouvre beaucoups de possibilités :ccool:
12/10/2010, 10h13
Waldar

Oui mais ça reste une rustine et rien de plus, officiellement c'est plus un bug qu'autre chose, rien ne garanti que ça continuera de fonctionner éternellement.

Créer une table calendrier reste la meilleure solution (100 ans ça ne fait jamais que 36000 lignes).
12/10/2010, 18h38
mnitu

Citation:

Envoyé par Waldar

Oui mais ça reste une rustine et rien de plus, officiellement c'est plus un bug qu'autre chose, rien ne garanti que ça continuera de fonctionner éternellement...

C'est très bien dit.
Par contre il existe toujours la possibilité de générer le calendrier en question via une fonction pipelined. Ainsi il sera facile de contrôler la finesse du calendrier en fonction des besoins.