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é

[farenheiit]

J'ai enlevé tous les hints existant dans tous les select des différentes plus celle de la vue principale et j'obtiebs 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
 
1	 
2	------------------------------------------------------------------------------------------------------------
3	| Id  | Operation                                  | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|
4	------------------------------------------------------------------------------------------------------------
5	|   0 | SELECT STATEMENT                           |                  |     1 |   316 |       |    12M  (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                             |                  |     1 |   316 |       |    12M  (1)|
12	|   7 |   NESTED LOOPS                             |                  |     1 |   316 |       |    12M  (1)|
13	|   8 |    NESTED LOOPS OUTER                      |                  |     1 |   312 |       |    12M  (1)|
14	|   9 |     NESTED LOOPS                           |                  |     1 |   305 |       |    12M  (1)|
15	|  10 |      NESTED LOOPS                          |                  |     1 |   300 |       |    12M  (1)|
16	|  11 |       NESTED LOOPS                         |                  |     1 |   292 |       |    12M  (1)|
17	|  12 |        NESTED LOOPS                        |                  |     1 |   278 |       |    12M  (1)|
18	|* 13 |         HASH JOIN                          |                  |    33 |  8778 |       |    12M  (1)|
19	|* 14 |          HASH JOIN                         |                  |    33 |  8481 |       |    12M  (1)|
20	|  15 |           NESTED LOOPS OUTER               |                  |    33 |  8151 |       |    12M  (1)|
21	|  16 |            NESTED LOOPS OUTER              |                  |    33 |  8019 |       |    12M  (1)|
22	|* 17 |             HASH JOIN                      |                  |    33 |  7887 |       |    12M  (1)|
23	|* 18 |              HASH JOIN                     |                  |     7 |   644 |       |    72   (2)|
24	|* 19 |               TABLE ACCESS BY INDEX ROWID  | IHSDEP           |     1 |    45 |       |     4   (0)|
25	|  20 |                NESTED LOOPS                |                  |    14 |  1190 |       |    69   (0)|
26	|* 21 |                 TABLE ACCESS BY INDEX ROWID| HISNEG           |    15 |   600 |       |     9   (0)|
27	|* 22 |                  INDEX RANGE SCAN          | HISNEG4          |    22 |       |       |     3   (0)|
28	|* 23 |                 INDEX RANGE SCAN           | IHSDEP1          |     1 |       |       |     3   (0)|
29	|  24 |               TABLE ACCESS BY INDEX ROWID  | INTERV           |     1 |     7 |       |     2   (0)|
30	|* 25 |                INDEX RANGE SCAN            | INTERV2          |     1 |       |       |     1   (0)|
31	|  26 |              VIEW                          | VCRDCL2          |   584M|    80G|       |    12M  (1)|
32	|  27 |               HASH GROUP BY                |                  |   584M|    48G|   137G|    12M  (1)|
33	|* 28 |                HASH JOIN                   |                  |   584M|    48G|       |   667K  (6)|
34	|  29 |                 MERGE JOIN CARTESIAN       |                  | 35200 |   859K|       |   186   (1)|
35	|  30 |                  MERGE JOIN CARTESIAN      |                  |   800 | 14400 |       |     9   (0)|
36	|* 31 |                   TABLE ACCESS FULL        | TYPSCR           |    25 |   200 |       |     2   (0)|
37	|  32 |                   BUFFER SORT              |                  |    32 |   320 |       |     7   (0)|
38	|  33 |                    TABLE ACCESS FULL       | PARMAR           |    32 |   320 |       |     0   (0)|
39	|  34 |                  BUFFER SORT               |                  |    44 |   308 |       |   185   (0)|
40	|  35 |                   TABLE ACCESS FULL        | DEVISE           |    44 |   308 |       |     0   (0)|
41	|* 36 |                 TABLE ACCESS FULL          | HISMVC           |   584M|    34G|       |   665K  (6)|
42	|* 37 |             INDEX UNIQUE SCAN              | MOIECH1          |     1 |     4 |       |     0   (0)|
43	|* 38 |            INDEX UNIQUE SCAN               | PORTEF2          |     1 |     4 |       |     0   (0)|
44	|  39 |           TABLE ACCESS FULL                | COMPTE           |   577 |  5770 |       |     5   (0)|
45	|  40 |          VIEW                              | index$_join$_020 |   464 |  4176 |       |     3   (0)|
46	|* 41 |           HASH JOIN                        |                  |       |       |       |            |
47	|  42 |            INDEX FAST FULL SCAN            | INTERV1          |   464 |  4176 |       |     1   (0)|
48	|  43 |            INDEX FAST FULL SCAN            | INTERV3          |   464 |  4176 |       |     1   (0)|
49	|* 44 |         INDEX UNIQUE SCAN                  | COMPTE1          |     1 |    12 |       |     0   (0)|
50	|  45 |        TABLE ACCESS BY INDEX ROWID         | NATACF           |     1 |    14 |       |     1   (0)|
51	|* 46 |         INDEX UNIQUE SCAN                  | NATACF1          |     1 |       |       |     0   (0)|
52	|* 47 |       INDEX UNIQUE SCAN                    | OBJCON1          |     1 |     8 |       |     0   (0)|
53	|* 48 |      INDEX UNIQUE SCAN                     | INTERV1          |     1 |     5 |       |     0   (0)|
54	|* 49 |     INDEX UNIQUE SCAN                      | INGRPI1          |     1 |     7 |       |     0   (0)|
55	|* 50 |    INDEX UNIQUE SCAN                       | COMPTE3          |     1 |     4 |       |     0   (0)|
56	------------------------------------------------------------------------------------------------------------
57	 
58	Predicate Information (identified by operation id):
59	---------------------------------------------------
60	 
61	   2 - filter("H"."RK"=1)
62	   3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."NUFDP","H"."CDCOF"
63	              ,"H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
64	   4 - filter("H"."DATRA"=:B1 AND "H"."DAVAL"=:B2 AND "H"."CDCOF"=:B3 AND ("H"."NCCOF"=5 OR 
65	              "H"."NCCOF"=6 OR "H"."NCCOF"=7))
66	   5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
67	  13 - access("I"."IDINT"="H"."ID_COINC")
68	  14 - access("C"."ID_NUCPT"="H"."ID_NUCPT")
69	  17 - access("V"."NUBIX"="H"."NUBIX" AND "V"."NUFDP"="H"."NUFDP")
70	       filter("V"."MTHGL"<>0 OR "H"."CTFDE"='N')
71	  18 - access("I"."COINT"="H"."COINI")
72	  19 - filter("H"."CTCPT"='C' AND ("H"."CTFDE"='A' OR "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR 
73	              "H"."CTFDE"='N' OR "H"."CTFDE"='S') AND "Z"."DATRA"="H"."DATRA")
74	  21 - filter("Z"."COINF"='GUP-HOLD' OR "Z"."COINF"='HK5DBKMC')
75	  22 - access("Z"."DATOP">=TO_DATE(' 2009-06-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
76	              "Z"."DATOP"<=TO_DATE(' 2009-06-29 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
77	  23 - access("Z"."NUBIX"="H"."NUBIX")
78	  25 - access("I"."CTINT"='I')
79	  28 - access("P"."COMAR"="X"."COMAR" AND "D"."CODEV"="X"."CDCOF" AND "S"."CTCOF"="X"."CTCOF")
80	  31 - filter("S"."COLAN"='A')
81	  36 - filter("X"."NCCOF"=5 OR "X"."NCCOF"=6 OR "X"."NCCOF"=7)
82	  37 - access("M"."CMECH"(+)="Z"."CMECH")
83	  38 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
84	  41 - access(ROWID=ROWID)
85	  44 - access("C"."COINT"="I"."COINT" AND "C"."NUCPT"="C"."NUCPT")
86	  46 - access("A"."CNACT"="Z"."CNACT")
87	  47 - access("O"."COBCN"="A"."COBCN")
88	  48 - access("J"."COINT"="I"."COINT")
89	  49 - access("W"."COINT"(+)="J"."COINT")
90	  50 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
91	 
92	Note
93	-----
94	   - 'PLAN_TABLE' is old version

Donc on a un COST qui passe de 17M à 12M.

[orafrance]

Faudrait essayer d'éradiquer les FTS sur TYPSCR, PARMAR, DEVISE et surtout HISMVC déjà

[farenheiit]

Pour TYPSCR, PARMAR, DEVISE le FTS n'est pas un pb car les tables sont toutes petites (voir les count(*) des tables que j'ai fourni).

Par contre Le FTS sur HISMVC n'est fait que si j'enlève le hint /*+ ORDERED INDEX_COMBINE(X)*/ dans la vue VCRDCL.
Si je laisse le hint l'optimizer passe par l'index bitmap mais qui ne filtre pas assez.
Moi je veux que HISMVC (qui se touve dans la vue VCRDCL) soit jointe avec la table HISNEG (qui se trouve dans une vue en ligne de la vue ECRDIC) en NESTED_LOOP via l'indexe HISMVC1 sur NUBIX comme dans le plan de la 9i.

[farenheiit]

si je force l'utilisation de l'index HISMVC1 dans la requête de la vue VCRDCL j'obtiens un Full index Scan.

La seule solution que j'ai trouvé c'est de mettre le hint /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0') */ au niveau de la requête principale et là j'obtiens le bon plan d'execution:

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
 
1	 
2	-------------------------------------------------------------------------------------------
3	| Id  | Operation                                       | Name    | Rows  | Bytes | Cost  |
4	-------------------------------------------------------------------------------------------
5	|   0 | SELECT STATEMENT                                |         |     1 |   225 |   106 |
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 |   106 |
12	|   7 |   NESTED LOOPS                                  |         |     1 |   225 |    95 |
13	|   8 |    NESTED LOOPS                                 |         |     1 |   217 |    95 |
14	|   9 |     NESTED LOOPS OUTER                          |         |     1 |   203 |    94 |
15	|  10 |      NESTED LOOPS                               |         |     1 |   196 |    94 |
16	|  11 |       NESTED LOOPS OUTER                        |         |     1 |   191 |    94 |
17	|  12 |        NESTED LOOPS                             |         |     1 |   187 |    94 |
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 UNIQUE SCAN                       | COMPTE1 |     1 |    12 |       |
53	|* 48 |        INDEX UNIQUE SCAN                        | MOIECH1 |     1 |     4 |       |
54	|* 49 |       INDEX UNIQUE SCAN                         | INTERV1 |     1 |     5 |       |
55	|* 50 |      INDEX UNIQUE SCAN                          | INGRPI1 |     1 |     7 |       |
56	|  51 |     TABLE ACCESS BY INDEX ROWID                 | NATACF  |     1 |    14 |     1 |
57	|* 52 |      INDEX UNIQUE SCAN                          | NATACF1 |     1 |       |       |
58	|* 53 |    INDEX UNIQUE SCAN                            | OBJCON1 |     1 |     8 |       |
59	-------------------------------------------------------------------------------------------
60	 
61	Predicate Information (identified by operation id):
62	---------------------------------------------------
63	 
64	   2 - filter("H"."RK"=1)
65	   3 - filter(RANK() OVER ( PARTITION BY "H"."DATRA","H"."DAVAL","H"."NUBIX","H"."N
66	              UFDP","H"."CDCOF","H"."NTUBX" ORDER BY "H"."CTCOF")<=1)
67	   4 - filter(("H"."NCCOF"=5 OR "H"."NCCOF"=6 OR "H"."NCCOF"=7) AND 
68	              "H"."DATRA"=:B1 AND "H"."DAVAL"=:B2 AND "H"."CDCOF"=:B3)
69	   5 - access("H"."NUBIX"=:B1 AND "H"."NUFDP"=:B2)
70	  14 - filter(SUM(DECODE("S"."IDFEE",'O',"UBXSTD"."ROUNDUP"(DECODE("X"."NCCOF",5,"X
71	              "."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF",0),DECODE("P"."ARRCO",0,ROUND(DECODE("X"."NCCO
72	              F",5,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC(DECODE("X"."NCCOF",5
73	              ,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',
74	              "UBXSTD"."ROUNDUP"(DECODE("X"."NCCOF",6,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF",0),D
75	              ECODE("P"."ARRCO",0,ROUND(DECODE("X"."NCCOF",6,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCO
76	              F","D"."FACAR"),TRUNC(DECODE("X"."NCCOF",6,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","
77	              D"."FACAR"))))+SUM(DECODE("S"."IDFEE",'O',"UBXSTD"."ROUNDUP"(DECODE("X"."NCCOF",7,"
78	              X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF",0),DECODE("P"."ARRCO",0,ROUND(DECODE("X"."NCC
79	              OF",7,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"),TRUNC(DECODE("X"."NCCOF",
80	              7,"X"."MTCOF",0)*"X"."QTCOF"*"X"."CSCOF","D"."FACAR"))))<>0 OR "H"."CTFDE"='N')
81	  26 - filter("Z"."COINF"='GUP-HOLD' OR "Z"."COINF"='HK5DBKMC')
82	  27 - access("Z"."DATOP">=TO_DATE(' 2009-06-29 00:00:00', 'syyyy-mm-dd 
83	              hh24:mi:ss') AND "Z"."DATOP"<=TO_DATE(' 2009-06-29 23:59:59', 'syyyy-mm-dd 
84	              hh24:mi:ss'))
85	  28 - filter(("H"."CTFDE"='A' OR "H"."CTFDE"='E' OR "H"."CTFDE"='L' OR 
86	              "H"."CTFDE"='N' OR "H"."CTFDE"='S') AND "H"."CTCPT"='C' AND 
87	              "Z"."DATRA"="H"."DATRA")
88	  29 - access("Z"."NUBIX"="H"."NUBIX")
89	  31 - access("P"."ID_COPOR"(+)="H"."ID_COPOR")
90	  33 - access("C"."ID_NUCPT"(+)="H"."ID_NUCPT")
91	  34 - access("A"."ID_NUCPT"="H"."ID_NUCPI")
92	  35 - filter("X"."NCCOF"=5 OR "X"."NCCOF"=6 OR "X"."NCCOF"=7)
93	  36 - access("X"."NUBIX"="H"."NUBIX" AND "X"."NUFDP"="H"."NUFDP")
94	  38 - access("S"."CTCOF"="X"."CTCOF" AND "S"."COLAN"='A')
95	  40 - access("P"."COMAR"="X"."COMAR")
96	  42 - access("D"."CODEV"="X"."CDCOF")
97	  43 - filter("I"."CTINT"='I')
98	  44 - access("I"."COINT"="H"."COINI")
99	  46 - access("I"."IDINT"="H"."ID_COINC")
100	  47 - access("C"."COINT"="X"."COINC" AND "C"."NUCPT"="X"."NUCPT")
101	  48 - access("M"."CMECH"(+)="X"."CMECH")
102	  49 - access("J"."COINT"="X"."COINC")
103	  50 - access("W"."COINT"(+)="J"."COINT")
104	  52 - access("A"."CNACT"="X"."CNACT")
105	  53 - access("O"."COBCN"="A"."COBCN")
106	 
107	Note
108	-----
109	   - 'PLAN_TABLE' is old version
110	   - cpu costing is off (consider enabling it)

Comment se fait il qu'en 10G il ne prenne pas le bon ? Y'a t'il un hint pour désactiver le cout by CPU et de ne laisser que le cost by I/O pour voir si c'est lié à ça. c'est peut être dû au fait que les stats systemes n'ont pas été calculé

[orafrance]

Le souci ce sont les hash join. Mais outre le plan, est-ce que la requête est réellement plus rapide ?