Discussion :

[mongolic]

Bonjour,

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

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

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

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

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

[mnitu]

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

Code :

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

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>

[mongolic]

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 ?

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

[mnitu]

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

[mongolic]

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 :

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

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

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

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

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

[mnitu]

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

[Garuda]

oracle fait ce qu'il veut

Le petit coquin !

[Waldar]

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 :

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

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.

[mnitu]

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 :

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

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.

[mongolic]

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

[Waldar]

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 :

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