Bonjour à tous,
je rencontre ce que je considère être un bug de l'optimiseur Oracle sur une 10g. Je voudrais savoir si pour vous j'ai fait une requête horrible qui plante naturellement Oracle ou si ça devrait marcher (même si on peut sûrement l'écrire mieux).
Donc j'ai 4 tables:
CLIENT contient le client, qui ne varie pas.
CONTRAT contient des contrats de prestation de service. Elle est liée à CLIENT en référençant l'ID de client. Il y a de 1 à 4 contrats par client dans la base mais il n'en existe jamais plus d'1 à la fois. Donc quand 1 contrat démarre, le précédent a été fermé (mais pour cet exemple on ne va pas s'en pré-occuper).
PRESTATION contient des prestations qui ont lieu à une date donnée. Elle est liée à CONTRAT en référençant l'ID de CONTRAT. Il y a de 1 à 4 prestations par contrat dans la base.
TARIF contient des tarifs définis sur des périodes pour chaque contrat. Elle est liée à CONTRAT en référençant l'ID de CONTRAT et il faut préciser 1 date pour trouver le tarif qui s'applique sur 1 contrat à 1 date. Il y a de 1 à 10 tarifs (et donc intervales de tarifs) par contrat.
Fonctionnellement il s'agit donc de clients qui passent des contrats pour des prestations, avec des tarifs qui peuvent varier dans le temps. Quand ils bénéficient d'une prestation, à une date donnée c'est le tarif lié à leur contrat à la date de la prestation qui s'applique.
La requête suivante sort la liste des clients, contrats, prestations et tarifs
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 "CLIENT" as ( Select '1ER CLIENT' "NAME", 1 "ID" from dual ), "CONTRAT" as ( Select '1ER CONTRAT' "NAME", 1 "ID", 1 "ID_CLIENT" from dual union Select '2E CONTRAT' "NAME", 2 "ID", 1 "ID_CLIENT" from dual ), "PRESTATION" as ( Select '1ER PRESTATION' "NAME", 1 "ID", 1 "ID_CONTRAT", sysdate-3 "DATE" from dual union Select '2E PRESTATION' "NAME", 2 "ID", 1 "ID_CONTRAT", sysdate-2 "DATE" from dual union Select '3E PRESTATION' "NAME", 3 "ID", 1 "ID_CONTRAT", sysdate-1 "DATE" from dual union Select '4E PRESTATION' "NAME", 4 "ID", 2 "ID_CONTRAT", sysdate-3 "DATE" from dual union Select '5E PRESTATION' "NAME", 5 "ID", 2 "ID_CONTRAT", sysdate-20 "DATE" from dual union Select '6E PRESTATION' "NAME", 6 "ID", 2 "ID_CONTRAT", sysdate-1 "DATE" from dual ), "TARIF" as ( Select 1 "ID_CONTRAT", sysdate-20 "DATE_START", sysdate-4 "DATE_END", 1 "TARIF" from dual union Select 1 "ID_CONTRAT", sysdate-4 "DATE_START", sysdate "DATE_END", 2 "TARIF" from dual union Select 2 "ID_CONTRAT", sysdate-20 "DATE_START", sysdate-4 "DATE_END", 3 "TARIF" from dual union Select 2 "ID_CONTRAT", sysdate-4 "DATE_START", sysdate "DATE_END", 4 "TARIF" from dual ) select * from "CLIENT" inner join "CONTRAT" on ("CLIENT"."ID" = "CONTRAT"."ID_CLIENT") inner join "PRESTATION" on ("CONTRAT"."ID" = "PRESTATION"."ID_CONTRAT") left outer join "TARIF" on ("CONTRAT"."ID" = "TARIF"."ID_CONTRAT" and "PRESTATION"."DATE" between "TARIF"."DATE_START" and "TARIF"."DATE_END") order by 2, 4, 7Ma problèmatique est de sortir pour chaque client, en 1 seule requête, les contrats et pour celui sur lequel a eu lieu la 1ere prestation du client (chronologiquement et pour tous les contrats confondus) le tarif qui s'est appliqué. Comme il s'agit en fait d'une requête générée par un générateur de requête tel que Business Object, je ne peux pas faire ce que je veux.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 NAME;ID;NAME_1;ID_1;ID_CLIENT;NAME_2;ID_2;ID_CONTRAT;DATE;ID_CONTRAT_1;DATE_START;DATE_END;TARIF 1ER CLIENT;1;1ER CONTRAT;1;1;1ER PRESTATION;1;1;28/03/2011 09:18:46;1;27/03/2011 09:18:46;31/03/2011 09:18:46;2 1ER CLIENT;1;1ER CONTRAT;1;1;2E PRESTATION;2;1;29/03/2011 09:18:46;1;27/03/2011 09:18:46;31/03/2011 09:18:46;2 1ER CLIENT;1;1ER CONTRAT;1;1;3E PRESTATION;3;1;30/03/2011 09:18:46;1;27/03/2011 09:18:46;31/03/2011 09:18:46;2 1ER CLIENT;1;2E CONTRAT;2;1;4E PRESTATION;4;2;28/03/2011 09:18:46;2;27/03/2011 09:18:46;31/03/2011 09:18:46;4 1ER CLIENT;1;2E CONTRAT;2;1;5E PRESTATION;5;2;11/03/2011 09:18:46;2;11/03/2011 09:18:46;27/03/2011 09:18:46;3 1ER CLIENT;1;2E CONTRAT;2;1;6E PRESTATION;6;2;30/03/2011 09:18:46;2;27/03/2011 09:18:46;31/03/2011 09:18:46;4
Bon gré mal gré je suis arrivé à faire ça: je calcule en 1 seule sous-requête, pour chaque client, la date de 1ere prestation et le contrat qui y correspond (en utilisant min("CONTRAT"."ID") KEEP (DENSE_RANK FIRST ORDER BY "PRESTATION"."DATE" ASC) ) et je joint cette "vue" contruite à la volée à ma table des tarifs pour en sortir le tarif du contrat (TARIF_FOR_FIRST_PREST), que je lie ensuite aux contrats et aux clients.
Encore une fois, oui on peut faire sûrement mieux, mais je travaille sur un générateur de code, les volumes sont faibles, et c'est plus maintenable parce que je peux réutiliser le bout de code à plusieurs endroits.
qui me donne ce résultat
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64 WITH "CLIENT" as ( Select '1ER CLIENT' "NAME", 1 "ID" from dual ), "CONTRAT" as ( Select '1ER CONTRAT' "NAME", 1 "ID", 1 "ID_CLIENT" from dual union Select '2E CONTRAT' "NAME", 2 "ID", 1 "ID_CLIENT" from dual ), "PRESTATION" as ( Select '1ER PRESTATION' "NAME", 1 "ID", 1 "ID_CONTRAT", sysdate-3 "DATE" from dual union Select '2E PRESTATION' "NAME", 2 "ID", 1 "ID_CONTRAT", sysdate-2 "DATE" from dual union Select '3E PRESTATION' "NAME", 3 "ID", 1 "ID_CONTRAT", sysdate-1 "DATE" from dual union Select '4E PRESTATION' "NAME", 4 "ID", 2 "ID_CONTRAT", sysdate-3 "DATE" from dual union Select '5E PRESTATION' "NAME", 5 "ID", 2 "ID_CONTRAT", sysdate-20 "DATE" from dual union Select '6E PRESTATION' "NAME", 6 "ID", 2 "ID_CONTRAT", sysdate-1 "DATE" from dual ), "TARIF" as ( Select 1 "ID_CONTRAT", sysdate-20 "DATE_START", sysdate-4 "DATE_END", 1 "TARIF" from dual union Select 1 "ID_CONTRAT", sysdate-4 "DATE_START", sysdate "DATE_END", 2 "TARIF" from dual union Select 2 "ID_CONTRAT", sysdate-20 "DATE_START", sysdate-4 "DATE_END", 3 "TARIF" from dual union Select 2 "ID_CONTRAT", sysdate-4 "DATE_START", sysdate "DATE_END", 4 "TARIF" from dual ) SELECT "CLIENT"."NAME" "Client Name", "CONTRAT"."NAME" "Contrat Name", "TARIF_FOR_FIRST_PREST"."TARIF" "Tarif", "CONTRAT"."ID", "TARIF_FOR_FIRST_PREST"."ID_CONTRAT" FROM "CLIENT" inner join "CONTRAT" on ("CLIENT"."ID" = "CONTRAT"."ID_CLIENT") left outer join (SELECT "TARIF"."ID_CONTRAT", "TARIF"."TARIF" FROM "TARIF" inner join (SELECT "CONTRAT"."ID_CLIENT", min("CONTRAT"."ID") KEEP (DENSE_RANK FIRST ORDER BY "PRESTATION"."DATE" ASC) "ID_ON_FIRST_DATE", min("PRESTATION"."DATE") "FIRST_DATE" FROM "CONTRAT" INNER JOIN "PRESTATION" ON ("CONTRAT"."ID" = "PRESTATION"."ID_CONTRAT") GROUP BY "CONTRAT"."ID_CLIENT" )"FIRST_PREST_DT_AND_CONTRACT_ID" on ( "TARIF"."ID_CONTRAT" = "FIRST_PREST_DT_AND_CONTRACT_ID"."ID_ON_FIRST_DATE" and "FIRST_PREST_DT_AND_CONTRACT_ID"."FIRST_DATE" between "TARIF"."DATE_START" and "TARIF"."DATE_END") ) "TARIF_FOR_FIRST_PREST" on ("TARIF_FOR_FIRST_PREST"."ID_CONTRAT" = "CONTRAT"."ID") WHERE "CLIENT"."NAME" = ('1ER CLIENT') order by 1
La 1ere prestation du client 1, qui a eu lieu à sysdate-20, était bien liée au 2e contrat et le tarif qui s'appliquait était bien 3.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 Client Name;Contrat Name;Tarif;ID;ID_CONTRAT 1ER CLIENT;2E CONTRAT;3;2;2 1ER CLIENT;1ER CONTRAT;;1;
Donc là ça marche très bien.
Mon problème c'est que si je remplace mes tables par de vraies tables, la requête me donne ce résultat:
Par contre si je ne restreint pas les résultats à UN client (donc j'enlève WHERE "CLIENT"."NAME" = ('1ER CLIENT'), il trouve bien mes données pour chaque client (dont client 1).
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 Client Name;Contrat Name;Tarif;ID;ID_CONTRAT 1ER CLIENT;2E CONTRAT;;2; 1ER CLIENT;1ER CONTRAT;;1;
Donc il ne trouve pas de tarif associé si je limite à 1 client. Mais si je remplace left outer join entre mes contrats et ma "vue" TARIF_FOR_FIRST_PREST par une inner join, il trouve bien mes données.
Pire, si je garde la jointure ouverte et que j'utilise un trunc sur l'ID pour "bloquer" l'optimiseur, il trouve bien mes données:
Enfin, si j'abandonne le min("CONTRAT"."ID") KEEP ... et que je fais une sous-requête classique, il trouve bien les données.
Code : Sélectionner tout - Visualiser dans une fenêtre à part on (trunc("TARIF_FOR_FIRST_PREST"."ID_CONTRAT") = "CONTRAT"."ID"
Ma conclusion est donc que, pour optimiser, Oracle ré-écrit ma requête et se trompe sur la "vue" où il y a des min("CONTRAT"."ID") KEEP ..., et que pour "désactiver" l'optimiseur il faut ajouter quelque chose pour l'empêcher de joindre les index entre eux, un trunc sur l'ID par exemple.
Pour moi il s'agit clairement d'un bug, et je vais faire avec. Mais je voulais avoir votre avis sur ma requête et savoir si elle était vraiment trop crade, et donc si c'est bien fait pour moi d'avoir ce problème, ou si elle est tolérable et donc Oracle mérite la corde.
Merci
10.2.0.4.0 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Partager