Optimisation de requête

**eikeshi** · 01/12/2008, 10h46

Bonjour à tous, je travaille sur un projet qui consiste à compter le nombre de ventes par transaction sur une base de données Oracle 9i.
Je dois compter le nombre de ventes, le nombre de retours, le nombre de tickets de ventes et le nombre de tickets de retours. Voici ma requête :

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
SELECT HC.CODEMAGASIN, HC.CODEVENDEUR,
(Select count(hc1.typedevente)
from historique_caisses hc1
where hc1.codemagasin = HC.CODEMAGASIN
and hc1.codevendeur = HC.CODEVENDEUR
and hc1.typedevente = 1
and hc1.typeligne = 1
and hc1.codeinternearticle >= 50000
and hc1.ticketannule = 0
and hc1.jourdevente like to_date('30/06/2008','DD/MM/YYYY')) AS NBARTICLESVENDUS,
(Select count(hc2.typedevente)
from historique_caisses hc2
where hc2.codemagasin = HC.CODEMAGASIN
and hc2.codevendeur = HC.CODEVENDEUR
and hc2.typedevente = 1
and hc2.typeligne = 2
and hc2.codeinternearticle >= 50000
and hc2.ticketannule = 0
and hc2.jourdevente like to_date('30/06/2008','DD/MM/YYYY')) AS NBARTICLESRETOURNES,
(select count(distinct hc3.numticket)
from historique_caisses hc3
where hc3.codemagasin = hc.codemagasin
and hc3.codevendeur = hc.codevendeur
and hc3.typedevente = 1
and hc3.codeinternearticle >= 50000
and hc3.typeligne = 1
and hc3.ticketannule = 0
and hc3.jourdevente like to_date('30/06/2008','DD/MM/YYYY')) AS NBTICKETSVENTES,
(select count(distinct hc4.numticket)
from historique_caisses hc4
where hc4.codemagasin = hc.codemagasin
and hc4.codevendeur = hc.codevendeur
and hc4.typedevente = 1
and hc4.codeinternearticle >= 50000
and hc4.typeligne = 2
and hc4.ticketannule = 0
and hc4.jourdevente like to_date('30/06/2008','DD/MM/YYYY')) AS NBTICKETSRETOURS
FROM HISTORIQUE_CAISSES HC
WHERE HC.CODEMAGASIN >= 33
AND HC.CODEINTERNEARTICLE >= 50000
AND HC.TYPELIGNE IN (1,2)
AND HC.JOURDEVENTE LIKE TO_DATE('30/06/2008','DD/MM/YYYY')
GROUP BY HC.CODEMAGASIN,HC.CODEVENDEUR

--------------------------------------------------------------------------
Cette requête fonctionne mais me paraît un peu trop lourde en traitement.
Est-ce qu'il y a un moyen d'optimiser cette dernière ?

Merci pour vos réponses.

**scheu** · 01/12/2008, 11h23

T'as lu les tutos sur ce site ?
Statistiques ? Plan d'exécution ? Quelle volumétrie ? T'as fais un tkprof ?

**eikeshi** · 01/12/2008, 11h47

Je ne vois pas en quoi les statistiques, le plan d'exécution, la volumétrie ou encore le tkprof serait utile ?
Je demande juste si, synthaxiquement parlant, il y a mieux que cette requête répétitive ?

**orafrance** · 01/12/2008, 13h53

Envoyé par eikeshi

Je demande juste si, synthaxiquement parlant, il y a mieux que cette requête répétitive ?

Et sans le plan d'exécution pour voir les accès comment tu veux qu'on te le dise ? On ne sait même pas si tu as des indexes sur tes tables

Tu peux déjà remplacer les LIKE par égal. typeligne n'est pas de type NUMBER ?

**orafrance** · 01/12/2008, 14h07

Ca donne quoi ça sinon :

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
SELECT HC.CODEMAGASIN, HC.CODEVENDEUR,
 DECODE(stats.typedevente || stats.codevendeur, '11', nb_tdv, 0)  AS NBARTICLESVENDUS,
 DECODE(stats.typedevente || stats.codevendeur, '12', nb_tdv, 0)  AS NBARTICLESRETOURNES,
 DECODE(stats.typedevente || stats.codevendeur, '11', nb_numtick, 0)  AS NBTICKETSVENTES,
 DECODE(stats.typedevente || stats.codevendeur, '12', nb_numtick, 0)  AS NBTICKETSRETOURS
FROM HISTORIQUE_CAISSES HC, 
(Select codemagasin,codevendeur,typedevente, typeligne,count(hc1.typedevente) nb_tdv,count(distinct hc3.numticket) nb_numtick
from historique_caisses hc1
where hc1.codeinternearticle >= '50000'
and hc1.ticketannule = '0'
and hc1.jourdevente = to_date('30/06/2008','DD/MM/YYYY')
GROUP BY typedevente,typeligne) stats
WHERE HC.CODEMAGASIN >= '33'
AND HC.CODEINTERNEARTICLE >= '50000'
AND HC.TYPELIGNE IN ('1','2')
AND HC.JOURDEVENTE = TO_DATE('30/06/2008','DD/MM/YYYY')
AND stats.codemagasin = HC.codemagasin
AND stats.codevendeur = HC.codevendeur
GROUP BY HC.CODEMAGASIN,HC.CODEVENDEUR

Voir :

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
SELECT HC.CODEMAGASIN, HC.CODEVENDEUR,
 DECODE(stats.typedevente || stats.codevendeur, '11', nb_tdv, 0)  AS NBARTICLESVENDUS,
 DECODE(stats.typedevente || stats.codevendeur, '12', nb_tdv, 0)  AS NBARTICLESRETOURNES,
 DECODE(stats.typedevente || stats.codevendeur, '11', nb_numtick, 0)  AS NBTICKETSVENTES,
 DECODE(stats.typedevente || stats.codevendeur, '12', nb_numtick, 0)  AS NBTICKETSRETOURS
FROM HISTORIQUE_CAISSES HC, 
(Select codemagasin,codevendeur,jourdevente,codeinternearticle,typedevente, typeligne,count(hc1.typedevente) nb_tdv,count(distinct hc3.numticket) nb_numtick
from historique_caisses hc1
where hc1.ticketannule = '0'
GROUP BY codemagasin,codevendeur,codeinternearticle,jourdevente,typedevente, typeligne) stats
WHERE HC.CODEMAGASIN >= '33'
AND stats.codemagasin = HC.codemagasin
AND stats.codevendeur = HC.codevendeur
AND stats.jourdevente = HC.jourdevente
AND stats.codeinternearticle = HC.codeinternearticle
AND HC.CODEINTERNEARTICLE >= '50000'
AND HC.TYPELIGNE IN ('1','2')
AND HC.JOURDEVENTE = TO_DATE('30/06/2008','DD/MM/YYYY')
GROUP BY HC.CODEMAGASIN,HC.CODEVENDEUR

Après, avec des fonctions analytiques bien sentie on peut probablement éviter de parcourir la table 2 fois.

**eikeshi** · 01/12/2008, 14h13

Ok désolé, je veux aller trop vite.
Voilà le scripts avec les indexes :

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
CREATE TABLE HISTORIQUE_CAISSES
(
  CODEMAGASIN            NUMBER(6)              NOT NULL,
  JOURDEVENTE            DATE                   NOT NULL,
  NUMTICKET              NUMBER(10)             NOT NULL,
  NUMLIGNE               NUMBER(3)              NOT NULL,
  CODEINTERNEARTICLE     NUMBER(9)              NOT NULL,
  CODECLIENT             VARCHAR2(20)           NOT NULL,
  CODECAISSE             VARCHAR2(2)            NOT NULL,
  CODECAISSIERE          NUMBER(13),
  CODEVENDEUR            NUMBER(13),
  TYPELIGNE              NUMBER(3),
  VOLVENTE               NUMBER(13,5),
  CANETREALISE           NUMBER(12,2),
  MARGENETTEREALISEE     NUMBER(12,2),
  MTREMISE               NUMBER(12,2),
  ENPROMOTION            NUMBER(1),
  NUMREMISE              NUMBER(6),
  CODETVA                VARCHAR2(1),
  MOTIFRETOUR            NUMBER(3),
  TICKETANNULE           NUMBER(1),
  MATRICULEPERSO         NUMBER(13),
  CODESAISIE             VARCHAR2(40),
  CODECARTE              VARCHAR2(20),
  DATERECHERCHETARIF     DATE,
  MTTVA                  NUMBER(12,2),
  CIAPOUBELLE            NUMBER(8),
  CODEDEVISE             VARCHAR2(3),
  DATEARCHIVAGE          DATE,
  TYPEDEVENTE            NUMBER(1),
  NUMPIECEIDENTITE       VARCHAR2(40),
  NBPOINTSASSOCIETICKET  NUMBER(8),
  CODESAISONGESTION      VARCHAR2(5),
  MTREMISEREEL           NUMBER(12,2),
  MTSURMARQUES           NUMBER(12,2),
  MTSOLDES               NUMBER(12,2),
  DATEENREGISTREMENT     DATE,
  NUMTICKETINIT          NUMBER(10),
  CODESESSION            NUMBER(9),
  MTREMISELIGNE          NUMBER(12,2),
  CODEACTIONMARKETING    NUMBER(10),
  IDTYPERESA             NUMBER(2),
  NUMLIGNERESA           NUMBER(2),
  STOCKMAGNONMAJ         NUMBER(1)
)
TABLESPACE DATA
LOGGING 
NOCACHE
NOPARALLEL;


CREATE INDEX HISTORIQUE_CAISSES_DAMFK2 ON HISTORIQUE_CAISSES
(CODEINTERNEARTICLE, IDTYPERESA, JOURDEVENTE, TICKETANNULE)
LOGGING
TABLESPACE INDX
NOPARALLEL;


CREATE UNIQUE INDEX HISTORIQUE_CAISSES_PK ON HISTORIQUE_CAISSES
(CODEMAGASIN, JOURDEVENTE, CODECAISSE, NUMTICKET, NUMLIGNE)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;

La volumétrie dépasse les 10 millions de lignes.
Au fait, tu avais raison pour le type NUMBER, encore une erreur du fait de vouloir aller trop vite.
Vous faut-il autre chose pour pouvoir m'aider ?

**orafrance** · 01/12/2008, 14h27

du coup :

Code :

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

DECODE(stats.typedevente || stats.codevendeur, '11', nb_tdv, 0)

Devient

Code :

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

DECODE(stats.typedevente , 1, DECODE(stats.codevendeur, 1, nb_tdv,0), 0)

Optimisation de requête

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager