Discussion :

[farenheiit]

Bonjour,

je travaille pour un editeur de progiciels financier et un de nos clients à une requête qui foire suite à la migration de sa base de la 9i vers la 10G.

Voici la requête qui pose 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
23
24
25
 
SELECT /*+ NO_MERGE(X) */
  '29/06/09',
  '29/06/09',
  X.COINI,
  X.COINT,
  NULL,
  X.CNACT,
  SUM(X.QTFDP * X.ISCHC),
  X.CODEV,
  SUM(X.MTHMK),
  SUM(X.MTVMK),
  SUM(X.MTHEX),
  SUM(X.MTVEX),
  SUM(X.MTHCP),
  SUM(X.MTVCP),
  SUM(X.MTHGL),
  SUM(X.MTVGL),
  SUM(X.MTTGL)
   FROM ECRDIC X
  WHERE X.DATOP >= TO_DATE('29062009 00:00:00', 'DDMMYYYY HH24:MI:SS')
    AND X.DATOP <= TO_DATE('29062009 23:59:59', 'DDMMYYYY HH24:MI:SS')
    AND X.COINF IN ('GUP-HOLD', 'HK5DBKMC')
  GROUP BY X.COINI, X.COINT, X.CNACT, X.CODEV
  ORDER BY 3, 4, 5, 8, 6;

ECRDIC est une vue. voici sa définition:

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
CREATE OR REPLACE VIEW ECRDIC
(coini, nmini, adini1, adini2, adini3, adini4, adini5, colai, ciini, coint, nmint, adint1, adint2, adint3, adint4, adint5, colac, ciint, cosie, cogcp, nucpt, rgcod, rgcid, ctcpt, catct, carea, lcpte, copcp, rmcpt, nubnk, cftcn, copor, idety, coinf, coinn, coope, coinl, comar, comsc, cnact, ntact, lnact, cnacn, cnama, caini, caloc, cdvac, ctacf, cobcn, ctstr, cnach, datra, datop, daval, csens, qtfdp, mtneg, mvneg, csopt, cmech, nmech, caech, mtsna, nuver, nuins, nucon, nubix, nufdp, nuord, nutic, ctfne, ctfic, coglo, cnaop, cntra, codev, mthmk, mtvmk, mthex, mtvex, mthcp, mtvcp, mthgx, mtvgx, mthgl, mtvgl, mttgl, copro, coinv, coina, couti, ctcot, cotsj, coink, ischc, daecb, tytrn, saima, corig, mtref)
AS
SELECT /*+ ORDERED NO_MERGE(X) USE_HASH(C J A O M) */
X.COINI,
X.NMINT,X.ADINT1,X.ADINT2,X.ADINT3,X.ADINT4,X.ADINT5,X.COLAN,X.CIINT,
X.COINC,
J.NMINT,J.ADINT1,J.ADINT2,J.ADINT3,J.ADINT4,J.ADINT5,J.COLAN,J.CIINT,
J.COSIE,W.COGCP,
X.NUCPT,C.RGCOD,C.RGCID,
C.CTCPT,C.CATCT,C.CAREA,C.LCPTE,C.COOPE,C.TEXTE,C.NUBNK,C.CFTCN,
X.COPOR,X.IDETY,X.COINF,X.COINN,X.COOPE,X.COINL,
X.COMAR,A.COMSC,
X.CNACT,
DECODE(A.CNACN,'T',0,1),A.LNACT,A.CNACN,A.CNAMA,A.CAINI,A.CALOC,A.CODEV,A.CTACF,A.COBCN,
X.CTSTR,X.CNACE,
X.DATRA,X.DATOP,X.DAVAL,X.CSENS,X.QTCOF,X.MTNEG,X.MVNEG,
X.CSOPT,X.CMECH,M.NMECH,X.CAECH,X.MTSNA,X.NUVER,X.NUINS,
X.NUCON,X.NUBIX,X.NUFDP,X.NUORD,X.NUTIC,X.CTFNE,X.CTFIC,X.COGLO,X.CNAOP,X.CNTRA,
X.CODEV,
X.MTHMK,X.MTVMK,X.MTHEX,X.MTVEX,X.MTHCP,X.MTVCP,
X.MTHGX,X.MTVGX,
X.MTHGL,X.MTVGL,X.MTTGL,
O.COPRO,X.COINV,X.COINA,X.COUTI,
A.CTCOT,A.COTSJ,X.COINK,X.ISCHC,X.DAECB,X.TYTRN,
DECODE(X.CORIG,NULL,'O','F','F','REG','REG','N'),X.CORIG,X.MTREF
FROM
 -- vue pour pouvoir utiliser HISMVC1 (clause X.MTHGL!=0 sortie)
(SELECT /*+ USE_NL(Y Z) */
 Y.COINI,
 I.NMINT,I.ADINT1,I.ADINT2,I.ADINT3,I.ADINT4,I.ADINT5,I.COLAN,I.CIINT,
 Y.COINC,
 Y.NUCPT,
 Y.COPOR,Y.IDETY,Z.COINF,Z.COINN,Z.COOPE,Y.COINL,
 Z.COMAR,
 Z.CNACT,
 Y.CTSTR,Y.CNACE,
 V.DATRA,Z.DATOP,V.DAVAL,Z.CSENS,V.QTCOF,Z.MTNEG,Z.MVNEG,
 Z.CSOPT,Z.CMECH,Z.CAECH,Z.MTSNA,Z.NUVER,Z.NUINS,
 Z.NUCON,Y.NUBIX,Y.NUFDP,Y.NUORD,Y.NUTIC,Y.CTFNE,Y.CTFDE,Y.CTFIC,Z.COGLO,Y.CNAOP,Y.CNTRA,
 V.CODEV,
 V.MTHMK,V.MTVMK,V.MTHEX,V.MTVEX,V.MTHCP,V.MTVCP,
 V.MTHGX,V.MTVGX,
 V.MTHGL,V.MTVGL,V.MTTGL,
 Y.COINV,Y.COINA,Y.COUTI,
 Z.COINK,V.ISCHC,Z.DAECB,Z.TYTRN,Z.CORIG,Z.MTREF
 FROM
 HISNEG Z,
 HISDEP Y,
 VCRDCL V,
 INTERV I
 WHERE
 Y.CTFDE IN ('N','L','A','S','E') AND
 Y.COINC IS NOT NULL              AND
 Y.CTCPT='C'                      AND
 Z.NUBIX=Y.NUBIX                  AND
 Z.DATRA=Y.DATRA                  AND -- perf
 V.NUBIX=Y.NUBIX                  AND
 V.NUFDP=Y.NUFDP                  AND
 I.COINT=Y.COINI                  AND
 I.CTINT='I'
) X,
COMPTE C,
INTERV J,
NATACF A,
OBJCON O,
MOIECH M,
INGRPI W
WHERE
(X.MTHGL!=0 OR X.CTFDE='N') AND
C.COINT=X.COINC             AND
C.NUCPT=X.NUCPT             AND
J.COINT=X.COINC             AND
A.CNACT=X.CNACT             AND
O.COBCN=A.COBCN             AND
M.CMECH(+)=X.CMECH          AND
W.COINT(+)=J.COINT

VCRDCL est une vue également et voici son contenu:

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
 
CREATE OR REPLACE VIEW VCRDCL
(datra, daval, nubix, nufdp, codev, qtcof, mthmk, mtvmk, mthex, mtvex, mthcp, mtvcp, mthgx, mtvgx, mthgl, mtvgl, mttgl, ischc)
AS
SELECT /*+ ORDERED INDEX_COMBINE(X)*/
X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF,
(SELECT ABS(SUM(DECODE(H.RK,1,DECODE(H.CTMVC,'N',0,'D',0,H.QTCOF*H.CSCOF),0)))
 FROM
 (SELECT H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,RANK() OVER(PARTITION BY H.DATRA,H.DAVAL,H.NUBIX,H.NUFDP,H.CDCOF,H.NTUBX ORDER BY H.CTCOF) RK,H.CTMVC,H.QTCOF,H.CSCOF
  FROM HISMVC H
  WHERE
  H.NCCOF IN (5,6,7)
 ) H
 WHERE
 H.DATRA=X.DATRA AND
 H.DAVAL=X.DAVAL AND
 H.NUBIX=X.NUBIX AND
 H.NUFDP=X.NUFDP AND
 H.CDCOF=X.CDCOF AND
 H.RK=1
) QTCOF,
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR)))),
0,0,
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR)))),
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,5,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,6,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,0)              ,DECODE(P.ARRCO,0,ROUND(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR)              ,TRUNC(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF,D.FACAR))))+
SUM(/*RNDTRC*/DECODE(S.IDFEE,'O',UBXSTD.ROUNDUP(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),0),DECODE(P.ARRTV,0,ROUND(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR),TRUNC(DECODE(X.NCCOF,7,X.MTCOF,0)*X.QTCOF*X.CSCOF*(X.TTCOF/100),D.FACAR)))),
MAX(DECODE(X.DATRA,X.DAORI,1,DECODE(X.CTMVC,'N',0,'D',0,1)))
FROM
HISMVC X,
PARMAR P,
DEVISE D,
TYPSCR S
WHERE
X.NCCOF IN (5,6,7) AND
P.COMAR=X.COMAR    AND
D.CODEV=X.CDCOF    AND
S.CTCOF=X.CTCOF    AND
S.COLAN='A'
GROUP BY
X.DATRA,X.DAVAL,X.NUBIX,X.NUFDP,X.CDCOF

HISDEP est une vue aussi:

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
 
CREATE OR REPLACE VIEW HISDEP
(nubix, comar, nucon, ncotc, csens, ctfne, ctfic, nufdp, nfotc, ctfde, coinc, nucpt, copor, ctcpt, coini, nucpi, cnaop, qtfdp, ctstr, cnace, cstra, nustr, nutic, lidep, cstcl, cstin, cstap, datra, corig, cdvrf, nugbx, cdvpc, nuord, coinl, cstal, ntubx, nubtr, nuftr, file1, file2, cover, cxord, crord, qtbas, cnbas, ctord, nuser, utext, crfop, couti, damod, cntra, file3, ciann, forex, coina, coinv, cstsa, mjmvc, nlubx, dltra, adjpo, coeas, cocti, guaps, idmod, nubio, nufdo, info1, info2, info3, info4, info5, cregr, idmrg, strat, oexid, otrid, onuor, daneo, daceo, mgrat, guref, isscr, dafcy, idety, nurge, nurgd, nurfd, norep, id_coinc, id_nucpt, id_nucpi, id_copor, autcv)
AS
SELECT
 H.NUBIX,H.COMAR,H.NUCON,H.NCOTC,H.CSENS,H.CTFNE,H.CTFIC,H.NUFDP,H.NFOTC,H.CTFDE,I.COINT,C.NUCPT,P.COPOR,
 H.CTCPT,H.COINI,A.NUCPT,H.CNAOP,H.QTFDP,H.CTSTR,H.CNACE,H.CSTRA,H.NUSTR,H.NUTIC,H.LIDEP,H.CSTCL,H.CSTIN,
 H.CSTAP,H.DATRA,H.CORIG,H.CDVRF,H.NUGBX,H.CDVPC,H.NUORD,H.COINL,H.CSTAL,H.NTUBX,H.NUBTR,H.NUFTR,H.FILE1,
 H.FILE2,H.COVER,H.CXORD,H.CRORD,H.QTBAS,H.CNBAS,H.CTORD,H.NUSER,H.UTEXT,H.CRFOP,H.COUTI,H.DAMOD,H.CNTRA,
 H.FILE3,H.CIANN,H.FOREX,H.COINA,H.COINV,H.CSTSA,H.MJMVC,H.NLUBX,H.DLTRA,H.ADJPO,H.COEAS,H.COCTI,H.GUAPS,
 H.IDMOD,H.NUBIO,H.NUFDO,H.INFO1,H.INFO2,H.INFO3,H.INFO4,H.INFO5,H.CREGR,H.IDMRG,H.STRAT,H.OEXID,H.OTRID,
 H.ONUOR,H.DANEO,H.DACEO,H.MGRAT,H.GUREF,H.ISSCR,H.DAFCY,H.IDETY,H.NURGE,H.NURGD,H.NURFD,H.NOREP,H.ID_COINC,
 H.ID_NUCPT,H.ID_NUCPI,H.ID_COPOR,H.AUTCV
FROM IHSDEP H,
     INTERV I,
     COMPTE C,
     COMPTE A,
     PORTEF P
WHERE 
     I.IDINT(+)   =H.ID_COINC AND
     C.ID_NUCPT(+)=H.ID_NUCPT AND
     A.ID_NUCPT   =H.ID_NUCPI AND
     P.ID_COPOR(+)=H.ID_COPOR

Le plan d'execution obtenu est le suivant:

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
-- Explain plan 10G
1	 
2	-------------------------------------------------------------------------------------------------------------
3	| Id  | Operation                                   | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|
4	-------------------------------------------------------------------------------------------------------------
5	|   0 | SELECT STATEMENT                            |                  |    33 |  7161 |       |    17M  (1)|
6	|   1 |  SORT AGGREGATE                             |                  |     1 |    88 |       |            |
7	|*  2 |   VIEW                                      |                  |     1 |    88 |       |     6  (17)|
8	|*  3 |    WINDOW SORT PUSHED RANK                  |                  |     1 |    51 |       |     6  (17)|
9	|*  4 |     TABLE ACCESS BY INDEX ROWID             | HISMVC           |     1 |    51 |       |     5   (0)|
10	|*  5 |      INDEX RANGE SCAN                       | HISMVC1          |     1 |       |       |     4   (0)|
11	|   6 |  SORT GROUP BY                              |                  |    33 |  7161 |       |    17M  (1)|
12	|   7 |   NESTED LOOPS OUTER                        |                  |    33 |  7161 |       |    17M  (1)|
13	|*  8 |    HASH JOIN OUTER                          |                  |    33 |  6930 |       |    17M  (1)|
14	|*  9 |     HASH JOIN                               |                  |    33 |  6798 |       |    17M  (1)|
15	|* 10 |      HASH JOIN                              |                  |    33 |  6534 |       |    17M  (1)|
16	|* 11 |       HASH JOIN                             |                  |    33 |  6072 |       |    17M  (1)|
17	|* 12 |        HASH JOIN                            |                  |    33 |  5907 |       |    17M  (1)|
18	|  13 |         VIEW                                |                  |    33 |  5511 |       |    17M  (1)|
19	|* 14 |          HASH JOIN                          |                  |    33 |  9801 |       |    17M  (1)|
20	|  15 |           NESTED LOOPS                      |                  |     7 |   945 |       |    81   (3)|
21	|* 16 |            HASH JOIN                        |                  |     7 |   917 |       |    81   (3)|
22	|* 17 |             HASH JOIN OUTER                 |                  |     7 |   854 |       |    77   (2)|
23	|  18 |              NESTED LOOPS OUTER             |                  |     7 |   784 |       |    72   (2)|
24	|* 19 |               HASH JOIN                     |                  |     7 |   756 |       |    72   (2)|
25	|* 20 |                TABLE ACCESS BY INDEX ROWID  | IHSDEP           |     1 |    45 |       |     4   (0)|
26	|  21 |                 NESTED LOOPS                |                  |    14 |  1414 |       |    69   (0)|
27	|* 22 |                  TABLE ACCESS BY INDEX ROWID| HISNEG           |    15 |   840 |       |     9   (0)|
28	|* 23 |                   INDEX RANGE SCAN          | HISNEG4          |    22 |       |       |     3   (0)|
29	|* 24 |                  INDEX RANGE SCAN           | IHSDEP1          |     1 |       |       |     3   (0)|
30	|  25 |                TABLE ACCESS BY INDEX ROWID  | INTERV           |     1 |     7 |       |     2   (0)|
31	|* 26 |                 INDEX RANGE SCAN            | INTERV2          |     1 |       |       |     1   (0)|
32	|* 27 |               INDEX UNIQUE SCAN             | PORTEF2          |     1 |     4 |       |     0   (0)|
33	|  28 |              TABLE ACCESS FULL              | COMPTE           |   577 |  5770 |       |     5   (0)|
34	|  29 |             VIEW                            | index$_join$_020 |   464 |  4176 |       |     3   (0)|
35	|* 30 |              HASH JOIN                      |                  |       |       |       |            |
36	|  31 |               INDEX FAST FULL SCAN          | INTERV1          |   464 |  4176 |       |     1   (0)|
37	|  32 |               INDEX FAST FULL SCAN          | INTERV3          |   464 |  4176 |       |     1   (0)|
38	|* 33 |            INDEX UNIQUE SCAN                | COMPTE3          |     1 |     4 |       |     0   (0)|
39	|  34 |           VIEW                              | VCRDCL           |   584M|    88G|       |    17M  (1)|
40	|  35 |            HASH GROUP BY                    |                  |   584M|    48G|   110G|    17M  (1)|
41	|* 36 |             HASH JOIN                       |                  |   584M|    48G|       |  5345K  (1)|
42	|* 37 |              TABLE ACCESS FULL              | TYPSCR           |    25 |   200 |       |     2   (0)|
43	|* 38 |              HASH JOIN                      |                  |   584M|    44G|       |  5343K  (1)|
44	|  39 |               TABLE ACCESS FULL             | DEVISE           |    44 |   308 |       |     2   (0)|
45	|* 40 |               HASH JOIN                     |                  |   584M|    40G|    41G|  5341K  (1)|
46	|  41 |                INLIST ITERATOR              |                  |       |       |       |            |
47	|  42 |                 TABLE ACCESS BY INDEX ROWID | HISMVC           |   584M|    34G|       |  3236K  (1)|
48	|  43 |                  BITMAP CONVERSION TO ROWIDS|                  |       |       |       |            |
49	|* 44 |                   BITMAP INDEX SINGLE VALUE | HISMVC6_BMP      |       |       |       |            |
50	|  45 |                TABLE ACCESS FULL            | PARMAR           |    32 |   320 |       |     2   (0)|
51	|  46 |         INDEX FAST FULL SCAN                | COMPTE1          |   577 |  6924 |       |     2   (0)|
52	|  47 |        INDEX FULL SCAN                      | INTERV1          |   464 |  2320 |       |     1   (0)|
53	|  48 |       TABLE ACCESS FULL                     | NATACF           |  2897 | 40558 |       |    24   (0)|
54	|  49 |      INDEX FAST FULL SCAN                   | OBJCON1          |  1422 | 11376 |       |     3   (0)|
55	|  50 |     INDEX FULL SCAN                         | MOIECH1          |    12 |    48 |       |     1   (0)|
56	|* 51 |    INDEX UNIQUE SCAN                        | INGRPI1          |     1 |     7 |       |     0   (0)|
57	-------------------------------------------------------------------------------------------------------------
58	 
59	Predicate Information (identified by operation id):
60	---------------------------------------------------
61	 
62	   2 - filter("H"."RK"=1)
63	   3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF",
64	              "H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
65	   4 - filter("H"."DATRA"=:B1 AND "H"."DAVAL"=:B2 AND "H"."CDCOF"=:B3 AND ("H"."NCCOF"=5 OR 
66	              "H"."NCCOF"=6 OR "H"."NCCOF"=7))
67	   5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
68	   8 - access("M"."CMECH"(+)="X"."CMECH")
69	   9 - access("O"."COBCN"="A"."COBCN")
70	  10 - access("A"."CNACT"="X"."CNACT")
71	  11 - access("J"."COINT"="X"."COINC")
72	  12 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
73	  14 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
74	       filter("V"."MTHGL"<>0 OR "H"."CTFDE"='N')
75	  16 - access("I"."IDINT"="H"."ID_COINC")
76	  17 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
77	  19 - access("I"."COINT"="H"."COINI")
78	  20 - filter("H"."CTCPT"='C' AND ("H"."CTFDE"='A' OR "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR 
79	              "H"."CTFDE"='N' OR "H"."CTFDE"='S') AND "Z"."DATRA"="H"."DATRA")
80	  22 - filter("Z"."COINF"='GUP-HOLD' OR "Z"."COINF"='HK5DBKMC')
81	  23 - access("Z"."DATOP">=TO_DATE(' 2009-06-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
82	              "Z"."DATOP"<=TO_DATE(' 2009-06-29 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
83	  24 - access("Z"."NUBIX"="H"."NUBIX")
84	  26 - access("I"."CTINT"='I')
85	  27 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
86	  30 - access(ROWID=ROWID)
87	  33 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
88	  36 - access("S"."CTCOF"="X"."CTCOF")
89	  37 - filter("S"."COLAN"='A')
90	  38 - access("D"."CODEV"="X"."CDCOF")
91	  40 - access("P"."COMAR"="X"."COMAR")
92	  44 - access("X"."NCCOF"=5 OR "X"."NCCOF"=6 OR "X"."NCCOF"=7)
93	  51 - access("W"."COINT"(+)="J"."COINT")
94	 
95	Note
96	-----
97	   - 'PLAN_TABLE' is old version

Si je passe le paramètre OPTIMIZER_FEATURES_ENABLE à 9.2.0 j'obtiens le plan d'execution suivant:

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
-- Explain plan 9i
1	 
2	-------------------------------------------------------------------------------------------
3	| Id  | Operation                                       | Name    | Rows  | Bytes | Cost  |
4	-------------------------------------------------------------------------------------------
5	|   0 | SELECT STATEMENT                                |         |     1 |   225 |   127 |
6	|   1 |  SORT AGGREGATE                                 |         |     1 |    88 |       |
7	|*  2 |   VIEW                                          |         |     1 |    88 |    17 |
8	|*  3 |    WINDOW SORT PUSHED RANK                      |         |     1 |    51 |    17 |
9	|*  4 |     TABLE ACCESS BY INDEX ROWID                 | HISMVC  |     1 |    51 |     5 |
10	|*  5 |      INDEX RANGE SCAN                           | HISMVC1 |     1 |       |     4 |
11	|   6 |  SORT GROUP BY                                  |         |     1 |   225 |   127 |
12	|   7 |   NESTED LOOPS OUTER                            |         |     1 |   225 |   116 |
13	|*  8 |    HASH JOIN OUTER                              |         |     1 |   218 |   116 |
14	|*  9 |     HASH JOIN                                   |         |     1 |   214 |   114 |
15	|* 10 |      HASH JOIN                                  |         |     1 |   206 |   111 |
16	|* 11 |       HASH JOIN                                 |         |     1 |   192 |    99 |
17	|* 12 |        HASH JOIN                                |         |     1 |   187 |    97 |
18	|  13 |         VIEW                                    |         |     1 |   175 |    94 |
19	|* 14 |          FILTER                                 |         |       |       |       |
20	|  15 |           SORT GROUP BY                         |         |     1 |   498 |    94 |
21	|  16 |            NESTED LOOPS                         |         |     1 |   498 |    82 |
22	|  17 |             NESTED LOOPS                        |         |     1 |   482 |    81 |
23	|  18 |              NESTED LOOPS                       |         |     1 |   384 |    80 |
24	|  19 |               NESTED LOOPS                      |         |     1 |   377 |    79 |
25	|  20 |                NESTED LOOPS                     |         |     1 |   367 |    78 |
26	|  21 |                 NESTED LOOPS                    |         |     1 |   359 |    77 |
27	|  22 |                  NESTED LOOPS                   |         |     1 |   295 |    71 |
28	|  23 |                   NESTED LOOPS OUTER            |         |     1 |   284 |    71 |
29	|  24 |                    NESTED LOOPS OUTER           |         |     1 |   267 |    70 |
30	|  25 |                     NESTED LOOPS                |         |     1 |   249 |    69 |
31	|* 26 |                      TABLE ACCESS BY INDEX ROWID| HISNEG  |    15 |  1935 |     9 |
32	|* 27 |                       INDEX RANGE SCAN          | HISNEG4 |    22 |       |     3 |
33	|* 28 |                      TABLE ACCESS BY INDEX ROWID| IHSDEP  |     1 |   120 |     4 |
34	|* 29 |                       INDEX RANGE SCAN          | IHSDEP1 |     1 |       |     3 |
35	|  30 |                     TABLE ACCESS BY INDEX ROWID | PORTEF  |     1 |    18 |     1 |
36	|* 31 |                      INDEX UNIQUE SCAN          | PORTEF2 |     1 |       |       |
37	|  32 |                    TABLE ACCESS BY INDEX ROWID  | COMPTE  |     1 |    17 |     1 |
38	|* 33 |                     INDEX UNIQUE SCAN           | COMPTE3 |     1 |       |       |
39	|* 34 |                   INDEX UNIQUE SCAN             | COMPTE3 |     1 |    11 |       |
40	|* 35 |                  TABLE ACCESS BY INDEX ROWID    | HISMVC  |     5 |   320 |     6 |
41	|* 36 |                   INDEX RANGE SCAN              | HISMVC1 |     1 |       |     3 |
42	|  37 |                 TABLE ACCESS BY INDEX ROWID     | TYPSCR  |     1 |     8 |     1 |
43	|* 38 |                  INDEX UNIQUE SCAN              | TYPSCR1 |     1 |       |       |
44	|  39 |                TABLE ACCESS BY INDEX ROWID      | PARMAR  |     1 |    10 |     1 |
45	|* 40 |                 INDEX UNIQUE SCAN               | PARMAR1 |     1 |       |       |
46	|  41 |               TABLE ACCESS BY INDEX ROWID       | DEVISE  |     1 |     7 |     1 |
47	|* 42 |                INDEX UNIQUE SCAN                | DEVISE1 |     1 |       |       |
48	|* 43 |              TABLE ACCESS BY INDEX ROWID        | INTERV  |     1 |    98 |     1 |
49	|* 44 |               INDEX UNIQUE SCAN                 | INTERV1 |     1 |       |       |
50	|  45 |             TABLE ACCESS BY INDEX ROWID         | INTERV  |     1 |    16 |     1 |
51	|* 46 |              INDEX UNIQUE SCAN                  | INTERV3 |     1 |       |       |
52	|  47 |         INDEX FAST FULL SCAN                    | COMPTE1 |   577 |  6924 |     2 |
53	|  48 |        INDEX FULL SCAN                          | INTERV1 |   464 |  2320 |     1 |
54	|  49 |       TABLE ACCESS FULL                         | NATACF  |  2897 | 40558 |    11 |
55	|  50 |      INDEX FAST FULL SCAN                       | OBJCON1 |  1422 | 11376 |     2 |
56	|  51 |     INDEX FULL SCAN                             | MOIECH1 |    12 |    48 |     1 |
57	|* 52 |    INDEX UNIQUE SCAN                            | INGRPI1 |     1 |     7 |       |
58	-------------------------------------------------------------------------------------------
59	 
60	Predicate Information (identified by operation id):
61	---------------------------------------------------
62	 
63	   2 - filter("H"."RK"=1)
64	   3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."N
65	              UFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
66	   4 - filter(("H"."NCCOF"=5 OR "H"."NCCOF"=6 OR "H"."NCCOF"=7) AND 
67	              "H"."DATRA"=:B1 AND "H"."DAVAL"=:B2 AND "H"."CDCOF"=:B3)
68	   5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
69	   8 - access("M"."CMECH"(+)="X"."CMECH")
70	   9 - access("O"."COBCN"="A"."COBCN")
71	  10 - access("A"."CNACT"="X"."CNACT")
72	  11 - access("J"."COINT"="X"."COINC")
73	  12 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
74	  14 - filter(SUM(DECODE("S"."IDFEE",'O',"UBXSTD"."ROUNDUP"(DECODE("X"."NCCOF",5,"X
75	              "."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF",0),DECODE("P"."ARRCO",0,ROUND(DECODE("X"."NCCO
76	              F",5,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC(DECODE("X"."NCCOF",5
77	              ,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',
78	              "UBXSTD"."ROUNDUP"(DECODE("X"."NCCOF",6,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF",0),D
79	              ECODE("P"."ARRCO",0,ROUND(DECODE("X"."NCCOF",6,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCO
80	              F","D"."FACAR"),TRUNC(DECODE("X"."NCCOF",6,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","
81	              D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',"UBXSTD"."ROUNDUP"(DECODE("X"."NCCOF",7,"
82	              X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF",0),DECODE("P"."ARRCO",0,ROUND(DECODE("X"."NCC
83	              OF",7,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC(DECODE("X"."NCCOF",
84	              7,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))<>0 OR "H"."CTFDE"='N')
85	  26 - filter("Z"."COINF"='GUP-HOLD' OR "Z"."COINF"='HK5DBKMC')
86	  27 - access("Z"."DATOP">=TO_DATE(' 2009-06-29 00:00:00', 'syyyy-mm-dd 
87	              hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-06-29 23:59:59', 'syyyy-mm-dd 
88	              hh24:mi:ss'))
89	  28 - filter(("H"."CTFDE"='A' OR "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR 
90	              "H"."CTFDE"='N' OR "H"."CTFDE"='S') AND "H"."CTCPT"='C' AND 
91	              "Z"."DATRA"="H"."DATRA")
92	  29 - access("Z"."NUBIX"="H"."NUBIX")
93	  31 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
94	  33 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
95	  34 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
96	  35 - filter("X"."NCCOF"=5 OR "X"."NCCOF"=6 OR "X"."NCCOF"=7)
97	  36 - access("X"."NUBIX"="H"."NUBIX" AND "X"."NUFDP"="H"."NUFDP")
98	  38 - access("S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A')
99	  40 - access("P"."COMAR"="X"."COMAR")
100	  42 - access("D"."CODEV"="X"."CDCOF")
101	  43 - filter("I"."CTINT"='I')
102	  44 - access("I"."COINT"="H"."COINI")
103	  46 - access("I"."IDINT"="H"."ID_COINC")
104	  52 - access("W"."COINT"(+)="J"."COINT")
105	 
106	Note
107	-----
108	   - 'PLAN_TABLE' is old version
109	   - cpu costing is off (consider enabling it)

On passe donc d'un COST de 17M à 127.
Par ailleurs lorsque je passe le paramètre _optimizer_cost_based_transformation à OFF j'obtiens le bon plan d'execution.

Voici le nb de lignes dans les tables traitées:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select table_name, num_rows from user_tables
where table_name in ('HISMVC','PARMAR','DEVISE','TYPSCR','HISNEG','IHSDEP','INTERV','INTERV','COMPTE','PORTEF');
 
1	COMPTE	577
2	HISMVC	1448247481
3	HISNEG	97277986
4	IHSDEP	127690809
5	INTERV	464
6	PARMAR	32
7	PORTEF	113
8	TYPSCR	75

La table DEVISE contient 44 lignes.


Je souhaiterais obtenir le bon plan d'execution sans avoir à changer ces paramètres.
En fait on se rend compte que le CBO 10G accède à la table HISMVC par l'index bitmap sur NCCOF ce qui permet de ne sélectionner qu' 1/4 de la table.
Mais 1/4 de 1.5 millard ça fait 500 millions de lignes ce qui fait beaucoup de lignes à merger avec les autres result set.
En 9i, le CBO filtre d'abord la table HISNEG car celle ci est fortement filtrée dans la requête pricipale:

Code :

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

1
2
3
4
 
WHERE X.DATOP >= TO_DATE('29062009 00:00:00', 'DDMMYYYY HH24:MI:SS')
    AND X.DATOP <= TO_DATE('29062009 23:59:59', 'DDMMYYYY HH24:MI:SS')
    AND X.COINF IN ('GUP-HOLD', 'HK5DBKMC')

Ensuite, il peut accéder à la table HISMVC via l'indexe unique HISMVC1 sur NUBIX car la vue VCRDCL (qui appelle la table HISMVC) est jointe avec HISNEG via le champ NUBIX.


Ma question est donc comment je peux faire via un hint pour que le CBO utilise le plan d'exécution de la 9i.

merci de votre aide.
je vais continuer à chercher de mon côté