Optimisation de procédure

**Superdub** · 20/05/2009, 10h36

Il existe en fait une table répertoriant les triplets de l'application :

Code :

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

1
2
3
4
5
6
 
create table RF_CPPDVREF (
ID integer not null, 
CIRCUITDEPREPARATION_CODE VARCHAR(30),
POINTDEVENTE_CODE VARCHAR(5), 
REFERENTIEL integer, primary key (ID));

De plus, il existe un index sur cette table :

Code :

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

1
2
3
4
 
CREATE UNIQUE INDEX RF_IDX_CPPDVREF 
	ON RF_CPPDVREF
(REFERENTIEL, CIRCUITDEPREPARATION_CODE, POINTDEVENTE_CODE) TABLESPACE IND;

De plus, j'ai essayé de refaire la requete pour prendre en compte les triplets qui ne sont pas présents dans la table RF_VOLUMEPREPREAL.

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
 
SELECT
  JOUR.DATE_CALENDRIER,
  REFER.CODE_ETABLISSEMENT, 
  REFER.ID AS ID_REF, 
  CPPDV.CIRCUITDEPREPARATION_CODE, 
  CPPDV.POINTDEVENTE_CODE,
  0,
  'UPDT'
FROM RF_REFERENTIEL REFER
INNER JOIN RF_JOUR JOUR ON 1=1
INNER JOIN RF_CPPDVREF CPPDV 
  ON CPPDV.REFERENTIEL = REFER.ID
LEFT OUTER JOIN RF_VOLUMEPREPREAL VOL 
  ON VOL.REFERENTIEL = REFER.ID
  AND VOL.CIRCUITPREPARATION = CPPDV.CIRCUITDEPREPARATION_CODE
  AND VOL.POINTDEVENTE = CPPDV.POINTDEVENTE_CODE
  AND VOL.JOUR = JOUR.date_calendrier
WHERE REFER.CODE='REF_REALISE'
AND jour.date_calendrier BETWEEN (SELECT MIN(VOL2.JOUR) FROM RF_VOLUMEPREPREAL VOL2 
                                  WHERE VOL2.circuitpreparation = CPPDV.CIRCUITDEPREPARATION_CODE
                                  AND VOL2.pointdevente = CPPDV.POINTDEVENTE_CODE
                                  AND VOL2.referentiel = REFER.ID)
                          AND (SELECT MAX(VOL3.JOUR) FROM RF_VOLUMEPREPREAL VOL3 
                                  WHERE VOL3.circuitpreparation = CPPDV.CIRCUITDEPREPARATION_CODE
                                  AND VOL3.pointdevente = CPPDV.POINTDEVENTE_CODE
                                  AND VOL3.referentiel = REFER.ID)
AND VOL.JOUR IS NULL;

Bon, il me reste à mettre une valeur par défaut pour les dates quand le triplet n'est pas présent dans la table.
Ça améliore grandement les performances initiales, par contre c'est beaucoup plus long que la requête de waldar ... (180 secondes au lieu de 10).
Peut être que le PARTITION BY y est pour quelque chose, mais je n'arrive pas à saisir le principe de cette instruction par rapport à ce que j'ai lu à droite et à gauche.

Si vous voyez des choses choquantes, ne vous gênez pas !

**Waldar** · 20/05/2009, 11h04

Le partition by permet en fait de créer des données lors d'une jointure externe.
Maintenant qu'il y a un moyen de connaître les triplets il n'y en a plus besoin.

Dans la requête suivante j'ai créé une sous-requête pour identifier tous les triplets désirés et au passage pour connaître les bornes d'extraction.

Ensuite je créé un produit cartésien avec la table calendrier et j'exclue tous les jours déjà existants.

Au final j'arrive à 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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT
  jour.date_calendrier,
  TPL.code_etablissement, 
  TPL.id_ref, 
  TPL.circuitdepreparation_code, 
  TPL.pointdevente_code,
  0,
  'UPDT'
FROM  
    (
    SELECT
        refer.id as id_ref,
        refer.code_etablissement,
        cppdv.circuitdepreparation_code,
        cppdv.pointdevente_code,
        min(vl1.jour) as j_min,
        max(vl1.jour) as j_max
    FROM
        rf_referentiel refer
        INNER JOIN rf_cppdvref cppdv 
          ON cppdv.referentiel = refer.id
        INNER JOIN rf_volumeprepreal vl1
          ON vl1.referentiel = refer.id
         AND vl1.pointdevente = cppdv.pointdevente_code
         AND vl1.circuitpreparation = cppdv.circuitdepreparation_code
    WHERE
        refer.code = 'REF_REALISE'
    GROUP BY    
        refer.id,
        refer.code_etablissement,
        cppdv.circuitdepreparation_code,
        cppdv.pointdevente_code
    ) TPL
    CROSS JOIN rf_jour jour
WHERE
    jour.date_calendrier between TPL.j_min AND TPL.j_max
AND NOT EXISTS (SELECT null FROM rf_volumeprepreal vl2
                WHERE vl2.referentiel = TPL.id_ref
                AND vl2.circuitpreparation = TPL.circuitdepreparation_code
                AND vl2.pointdevente = TPL.pointdevente_code
                AND vl2.jour = jour.date_calendrier)

Le not exists est identique à une jointure externe + <champ> is null, mais je le trouve plus clair en relecture.

**dgi77** · 20/05/2009, 12h23

Waldar, je suis à la fois intrigué et intéressé par le PARTITION BY tel que tu l'as codé. J'ai essayé de comprendre sur de petits exemples, mais il ne se passe rien de plus que si je ne le mets pas.
Je connaissais ça dans les fonction analytiques, mais dans la clause SELECT d'une requête. Par contre, dans la clause FROM, je n'avais jamais vu, mais comme la syntaxe de la requête semble correcte, je suppose que ça ouvre certaines possibilités...
Peux-tu nous expliquer un peu plus exhaustivement ?
Désolé pour Superdub si je m'écarte un peu du sujet initial, ce ne sera qu'une parenthèse

**Waldar** · 20/05/2009, 13h10

Aucun problème, je la mettrai dans mon blog ainsi.
C'est ce qu'on appelle un Partitioned Outer Joins.

Voici le lien vers la documentation Oracle :
http://download.oracle.com/docs/cd/B...2.htm#i2177515

Je vais reprendre l'exemple de la documentation et simuler deux tables avec un WITH :
une table calendrier (de sept jours) et une table de vente.

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
with temps as
(
select date '2009-05-01' + level - 1 as jour
from dual connect by level <= 7
),
vente as
(
select date '2009-05-01' as jour, 'Voiture' as produit, 100 as montant from dual union all
select date '2009-05-03', 'Voiture', 75 from dual union all
select date '2009-05-07', 'Voiture', 80 from dual union all
select date '2009-05-01', 'Velo'   ,  8 from dual union all
select date '2009-05-02', 'Velo'   ,  5 from dual union all
select date '2009-05-06', 'Velo'   , 15 from dual union all
select date '2009-05-07', 'Velo'   ,  3 from dual
)
select * from temps
-- select * from vente

Par la suite je ne remets pas le WITH pour ne pas prendre trop de place.

Si je fais une jointure forte, je n'ai pas de valeur ajoutée par rapport à la table vente :

Code :

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

1
2
3
4
5
select t.jour, v.produit, v.montant
from
    vente v
    inner join temps t
      on t.jour = v.jour

Code :

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

1
2
3
4
5
6
7
8
JOUR		PRODUIT	MONTANT
01/05/2009	Voiture	100
03/05/2009	Voiture	75
07/05/2009	Voiture	80
01/05/2009	Velo	8
02/05/2009	Velo	5
06/05/2009	Velo	15
07/05/2009	Velo	3

Si je fais une jointure externe, je récupère deux jours sur lesquels je n'avais eu aucune vente :

Code :

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

1
2
3
4
5
select t.jour, v.produit, nvl(v.montant, 0) as montant
from
    vente v
    right outer join temps t
      on t.jour = v.jour

Code :

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

1
2
3
4
5
6
7
8
9
10
JOUR		PRODUIT	MONTANT
01/05/2009	Voiture	100
03/05/2009	Voiture	75
07/05/2009	Voiture	80
01/05/2009	Velo	8
02/05/2009	Velo	5
06/05/2009	Velo	15
07/05/2009	Velo	3
04/05/2009		0
05/05/2009		0

Mais ce n'est pas complètement satisfaisant car je n'ai pas de valeur de produit.
La jointure externe partitionnée permet de faire une jointure externe sur tous les jours pour chaque produit :

Code :

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

1
2
3
4
5
select t.jour, v.produit, nvl(v.montant, 0) as montant
from
    vente v PARTITION BY (produit)
    right outer join temps t
      on t.jour = v.jour

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
JOUR		PRODUIT	MONTANT
01/05/2009	Velo	8
02/05/2009	Velo	5
03/05/2009	Velo	0
04/05/2009	Velo	0
05/05/2009	Velo	0
06/05/2009	Velo	15
07/05/2009	Velo	3
01/05/2009	Voiture	100
02/05/2009	Voiture	0
03/05/2009	Voiture	75
04/05/2009	Voiture	0
05/05/2009	Voiture	0
06/05/2009	Voiture	0
07/05/2009	Voiture	80

Et c'est beaucoup plus exploitable pour faire du reporting !

**Superdub** · 20/05/2009, 15h11

Merci waldar, ta façon de faire est plus rapide (dans les 15 secondes environ).

Je corse en peu l'affaire.

La valeur 'UPDT' change selon deux cas :

Elle vaut 'UPDT' pour les jours compris entre MIN et MAX DATE, elle vaut 'NUPDT' entre MAX DATE +1 et SYSDATE.

Je pense qu'avec le temps gagné je peux me permettre d'exécuter deux fois la requête en changeant les bornes des dates, mais si on peut tout grouper dans la même requête, pourquoi pas.

En tout cas merci, vos aides me sont précieuses.

Petite question, pour les triplets qui ne sont pas dans la table, pour récupérer une valeur de date, il faut bien que j'utilise la fonction DECODE sur null ?

**Waldar** · 20/05/2009, 17h18

Que voulez-vous prendre comme borne si le triplet n'existe pas ?

Pour le code, pas de soucis mais on filtre toujours entre date_min et date_max donc soit ce sera toujours 'UPDT' soit j'ai manqué une étape.

**dgi77** · 20/05/2009, 19h10

Merci Waldar pour la petite démonstration sur les PARTITION BY : c'est plus clair, et ça peut être utile

J'ai aussi récupéré ta requête pour générer le calendrier, mais c'est dur à adapter sur plusieurs mois : est-ce que tu as la solution, car ça aussi c'est très utile !

Enfin, le WITH, quelle utilité ?

Merci.

**Waldar** · 20/05/2009, 20h39

J'en ai profité pour modifier la formule du calendrier, c'est plus simple ainsi et vous l'adapterez probablement beaucoup plus facilement. Mettez la date de début qui convient ainsi que la durée que vous voulez.

Attention néanmoins, la syntaxe connect by level est un effet non désiré de connect by, ce n'est aucunement supporté par Oracle. Je m'en sers pour créer des jeux de test, mais en aucun cas c'est à déployer en production.
Ce paragraphe est entièrement copyrighté LaurentSchneider

Je vous conseille d'avoir de toutes façons une table calendrier, vous alimentez la durée qui va bien (100 ans si vous voulez, ça ne fait au final que 37000 lignes) soit avec une requête soit avec une procédure stockée.

Mettre les jeux d'essais dans une CTE permet simplement à ceux qui veulent reproduire le test de faire un copier / coller sans avoir à modifier quoi que ce soit dans la base de données. On pourrait effectivement écrire des sous-requêtes directement dans le FROM, mais la lecture est plus aisée ainsi.

Si vous lisez l'anglais j'ai publié la même chose en très légèrement plus complet sur mon blog :
http://www.waldar.org/blog/200905/pa...ed-outer-joins

**dgi77** · 22/05/2009, 11h44

Merci beaucoup Waldar

La requête pour générer les dates n'est peut-être pas très académique, mais elle déchire et peut être très utile...

D'ailleurs, cette question a été posée maintes fois sur le forum, mais je n'ai jamais vu quelqu'un y répondre, ça devrait pouvoir dépanner du monde...

Optimisation de procédure

PL/SQL Oracle

Discussions similaires

Partager

Partager