在解决项目性能问题的时候，遇到了一个奇怪的问题。在9i上走的分区和索引，但是到了11g下只是走了分区。不清楚是什么原因造成的，希望高手帮忙指点下。
建表语句如下：
-- Create table
create table T_IVRA_DAY_AUTOKEYTRACE
(
LOGDATE DATE not null,
PARTID CHAR(4) not null,
CALLEENO VARCHAR2(20),
CITYID VARCHAR2(20),
CITYCODE VARCHAR2(5) default '00',
USERGRADE NUMBER(4),
USERTYPE NUMBER(4),
OPERATETRACE VARCHAR2(30),
OPERATELAYER NUMBER(4) default 0,
AUTOSERVICE VARCHAR2(20),
AUTOSERVICENAME VARCHAR2(100),
SUCCCOUNT NUMBER(10),
FAILCOUNT NUMBER(10),
REPLAYCOUNT NUMBER(10),
UPCLASSCOUNT NUMBER(10),
UPTOPCOUNT NUMBER(10),
TOMANCOUNT NUMBER(10),
SMSCOUNT NUMBER(10),
SMSSUCCCOUNT NUMBER(10),
EMAILCOUNT NUMBER(10),
EMAILSUCCCOUNT NUMBER(10),
FAXCOUNT NUMBER(10),
FAXSUCCCOUNT NUMBER(10),
TOSRVCOUNT NUMBER(10),
HANGUPCOUNT NUMBER(10),
SUCCHANGUPCOUNT NUMBER(10),
FAILHANGUPCOUNT NUMBER(10)
)
partition by range (PARTID)
(
partition T_IVRA_DAY_TRACE_0101 values less than ('0110'),
partition T_IVRA_DAY_TRACE_0110 values less than ('0120'),
partition T_IVRA_DAY_TRACE_0120 values less than ('0201'),
partition T_IVRA_DAY_TRACE_0201 values less than ('0210'),
partition T_IVRA_DAY_TRACE_0210 values less than ('0220'),
partition T_IVRA_DAY_TRACE_0220 values less than ('0301'),
partition T_IVRA_DAY_TRACE_0301 values less than ('0310'),
partition T_IVRA_DAY_TRACE_0310 values less than ('0320'),
partition T_IVRA_DAY_TRACE_0320 values less than ('0401'),
partition T_IVRA_DAY_TRACE_0401 values less than ('0410'),
partition T_IVRA_DAY_TRACE_0410 values less than ('0420'),
partition T_IVRA_DAY_TRACE_0420 values less than ('0501'),
partition T_IVRA_DAY_TRACE_0501 values less than ('0510'),
partition T_IVRA_DAY_TRACE_0510 values less than ('0520'),
partition T_IVRA_DAY_TRACE_0520 values less than ('0601'),
partition T_IVRA_DAY_TRACE_0601 values less than ('0610'),
partition T_IVRA_DAY_TRACE_0610 values less than ('0620'),
partition T_IVRA_DAY_TRACE_0620 values less than ('0701'),
partition T_IVRA_DAY_TRACE_0701 values less than ('0710'),
partition T_IVRA_DAY_TRACE_0710 values less than ('0720'),
partition T_IVRA_DAY_TRACE_0720 values less than ('0801'),
partition T_IVRA_DAY_TRACE_0801 values less than ('0810'),
partition T_IVRA_DAY_TRACE_0810 values less than ('0820'),
partition T_IVRA_DAY_TRACE_0820 values less than ('0901'),
partition T_IVRA_DAY_TRACE_0901 values less than ('0910'),
partition T_IVRA_DAY_TRACE_0910 values less than ('0920'),
partition T_IVRA_DAY_TRACE_0920 values less than ('1001'),
partition T_IVRA_DAY_TRACE_1001 values less than ('1010'),
partition T_IVRA_DAY_TRACE_1010 values less than ('1020'),
partition T_IVRA_DAY_TRACE_1020 values less than ('1101'),
partition T_IVRA_DAY_TRACE_1101 values less than ('1110'),
partition T_IVRA_DAY_TRACE_1110 values less than ('1120'),
partition T_IVRA_DAY_TRACE_1120 values less than ('1201'),
partition T_IVRA_DAY_TRACE_1201 values less than ('1210'),
partition T_IVRA_DAY_TRACE_1210 values less than ('1220'),
partition T_IVRA_DAY_TRACE_1220 values less than (MAXVALUE)
;
建表之后创建了两个分区索引。创建语句如下create index IX_IVRA_DAY_AUTOKEYTRACE_q on T_IVRA_DAY_AUTOKEYTRACE ( USERTYPE, CALLEENO, CITYCODE,OPERATETRACE, LOGDATE)
local
(
partition idx_type_101 tablespace TBS_CSP_CC_IDX,
partition idx_type_110 tablespace TBS_CSP_CC_IDX,
partition idx_type_120 tablespace TBS_CSP_CC_IDX,
partition idx_type_201 tablespace TBS_CSP_CC_IDX,
partition idx_type_210 tablespace TBS_CSP_CC_IDX,
partition idx_type_220 tablespace TBS_CSP_CC_IDX,
partition idx_type_301 tablespace TBS_CSP_CC_IDX,
partition idx_type_310 tablespace TBS_CSP_CC_IDX,
partition idx_type_320 tablespace TBS_CSP_CC_IDX,
partition idx_type_401 tablespace TBS_CSP_CC_IDX,
partition idx_type_410 tablespace TBS_CSP_CC_IDX,
partition idx_type_420 tablespace TBS_CSP_CC_IDX,
partition idx_type_501 tablespace TBS_CSP_CC_IDX,
partition idx_type_510 tablespace TBS_CSP_CC_IDX,
partition idx_type_520 tablespace TBS_CSP_CC_IDX,
partition idx_type_601 tablespace TBS_CSP_CC_IDX,
partition idx_type_610 tablespace TBS_CSP_CC_IDX,
partition idx_type_620 tablespace TBS_CSP_CC_IDX,
partition idx_type_701 tablespace TBS_CSP_CC_IDX,
partition idx_type_710 tablespace TBS_CSP_CC_IDX,
partition idx_type_720 tablespace TBS_CSP_CC_IDX,
partition idx_type_801 tablespace TBS_CSP_CC_IDX,
partition idx_type_810 tablespace TBS_CSP_CC_IDX,
partition idx_type_820 tablespace TBS_CSP_CC_IDX,
partition idx_type_901 tablespace TBS_CSP_CC_IDX,
partition idx_type_910 tablespace TBS_CSP_CC_IDX,
partition idx_type_920 tablespace TBS_CSP_CC_IDX,
partition idx_type_1001 tablespace TBS_CSP_CC_IDX,
partition idx_type_1010 tablespace TBS_CSP_CC_IDX,
partition idx_type_1020 tablespace TBS_CSP_CC_IDX,
partition idx_type_1101 tablespace TBS_CSP_CC_IDX,
partition idx_type_1110 tablespace TBS_CSP_CC_IDX,
partition idx_type_1120 tablespace TBS_CSP_CC_IDX,
partition idx_type_1201 tablespace TBS_CSP_CC_IDX,
partition idx_type_1210 tablespace TBS_CSP_CC_IDX,
partition idx_type_1220 tablespace TBS_CSP_CC_IDX
);create index IX_IVRA_DAY_AUTOKEYTRACE_RE on T_IVRA_DAY_AUTOKEYTRACE (CALLEENO, CITYCODE, USERGRADE, OPERATETRACE, OPERATELAYER, SUCCCOUNT, FAILCOUNT, LOGDATE)
local
(
partition idx_re_101 tablespace TBS_CSP_CC_IDX,
partition idx_re_110 tablespace TBS_CSP_CC_IDX,
partition idx_re_120 tablespace TBS_CSP_CC_IDX,
partition idx_re_201 tablespace TBS_CSP_CC_IDX,
partition idx_re_210 tablespace TBS_CSP_CC_IDX,
partition idx_re_220 tablespace TBS_CSP_CC_IDX,
partition idx_re_301 tablespace TBS_CSP_CC_IDX,
partition idx_re_310 tablespace TBS_CSP_CC_IDX,
partition idx_re_320 tablespace TBS_CSP_CC_IDX,
partition idx_re_401 tablespace TBS_CSP_CC_IDX,
partition idx_re_410 tablespace TBS_CSP_CC_IDX,
partition idx_re_420 tablespace TBS_CSP_CC_IDX,
partition idx_re_501 tablespace TBS_CSP_CC_IDX,
partition idx_re_510 tablespace TBS_CSP_CC_IDX,
partition idx_re_520 tablespace TBS_CSP_CC_IDX,
partition idx_re_601 tablespace TBS_CSP_CC_IDX,
partition idx_re_610 tablespace TBS_CSP_CC_IDX,
partition idx_re_620 tablespace TBS_CSP_CC_IDX,
partition idx_re_701 tablespace TBS_CSP_CC_IDX,
partition idx_re_710 tablespace TBS_CSP_CC_IDX,
partition idx_re_720 tablespace TBS_CSP_CC_IDX,
partition idx_re_801 tablespace TBS_CSP_CC_IDX,
partition idx_re_810 tablespace TBS_CSP_CC_IDX,
partition idx_re_820 tablespace TBS_CSP_CC_IDX,
partition idx_re_901 tablespace TBS_CSP_CC_IDX,
partition idx_re_910 tablespace TBS_CSP_CC_IDX,
partition idx_re_920 tablespace TBS_CSP_CC_IDX,
partition idx_re_1001 tablespace TBS_CSP_CC_IDX,
partition idx_re_1010 tablespace TBS_CSP_CC_IDX,
partition idx_re_1020 tablespace TBS_CSP_CC_IDX,
partition idx_re_1101 tablespace TBS_CSP_CC_IDX,
partition idx_re_1110 tablespace TBS_CSP_CC_IDX,
partition idx_re_1120 tablespace TBS_CSP_CC_IDX,
partition idx_re_1201 tablespace TBS_CSP_CC_IDX,
partition idx_re_1210 tablespace TBS_CSP_CC_IDX,
partition idx_re_1220 tablespace TBS_CSP_CC_IDX
);执行sql为
SELECT
--T.USERTYPE USERTYPE,
SUM(T.SUCCCOUNT)/10000 SUCCCOUNT,
SUM(T.FAILCOUNT)/10000 FAILCOUNT,
SUM(T.TOMANCOUNT)/10000 TOMANCOUNT,
SUM(T.REPLAYCOUNT)/10000 REPLAYCOUNT,
SUM(T.UPCLASSCOUNT)/10000 UPCLASSCOUNT,
SUM(T.UPTOPCOUNT)/10000 UPTOPCOUNT,
SUM(T.SUCCHANGUPCOUNT)/10000 SUCCHANGUPCOUNT,
SUM(T.FAILHANGUPCOUNT)/10000 FAILHANGUPCOUNT
FROM T_IVRA_DAY_AUTOKEYTRACE T
WHERE
T.USERTYPE = '0'
and T.CALLEENO = '1'
AND T.CITYCODE = '1'
-- AND T.USERTYPE IS NOT NULL
AND T.OPERATETRACE like '1' || '%'
AND T.LOGDATE >= TO_DATE('2009-08-01' ||' 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND T.LOGDATE <= TO_DATE('2009-08-31' ||' 23:59:59','YYYY-MM-DD HH24:MI:SS')
and TO_CHAR(T.LOGDATE,'HH24:MI:SS') >= '00:00:00'
and TO_CHAR(T.LOGDATE,'HH24:MI:SS') <= '23:59:59'
and t.partid >= '0801'
and t.partid <= '0820'
同样的代码，为什么在9i和11g上的执行计划会不一样呢？
图片不会上传啊...郁闷
建表语句如下：
-- Create table
create table T_IVRA_DAY_AUTOKEYTRACE
(
LOGDATE DATE not null,
PARTID CHAR(4) not null,
CALLEENO VARCHAR2(20),
CITYID VARCHAR2(20),
CITYCODE VARCHAR2(5) default '00',
USERGRADE NUMBER(4),
USERTYPE NUMBER(4),
OPERATETRACE VARCHAR2(30),
OPERATELAYER NUMBER(4) default 0,
AUTOSERVICE VARCHAR2(20),
AUTOSERVICENAME VARCHAR2(100),
SUCCCOUNT NUMBER(10),
FAILCOUNT NUMBER(10),
REPLAYCOUNT NUMBER(10),
UPCLASSCOUNT NUMBER(10),
UPTOPCOUNT NUMBER(10),
TOMANCOUNT NUMBER(10),
SMSCOUNT NUMBER(10),
SMSSUCCCOUNT NUMBER(10),
EMAILCOUNT NUMBER(10),
EMAILSUCCCOUNT NUMBER(10),
FAXCOUNT NUMBER(10),
FAXSUCCCOUNT NUMBER(10),
TOSRVCOUNT NUMBER(10),
HANGUPCOUNT NUMBER(10),
SUCCHANGUPCOUNT NUMBER(10),
FAILHANGUPCOUNT NUMBER(10)
)
partition by range (PARTID)
(
partition T_IVRA_DAY_TRACE_0101 values less than ('0110'),
partition T_IVRA_DAY_TRACE_0110 values less than ('0120'),
partition T_IVRA_DAY_TRACE_0120 values less than ('0201'),
partition T_IVRA_DAY_TRACE_0201 values less than ('0210'),
partition T_IVRA_DAY_TRACE_0210 values less than ('0220'),
partition T_IVRA_DAY_TRACE_0220 values less than ('0301'),
partition T_IVRA_DAY_TRACE_0301 values less than ('0310'),
partition T_IVRA_DAY_TRACE_0310 values less than ('0320'),
partition T_IVRA_DAY_TRACE_0320 values less than ('0401'),
partition T_IVRA_DAY_TRACE_0401 values less than ('0410'),
partition T_IVRA_DAY_TRACE_0410 values less than ('0420'),
partition T_IVRA_DAY_TRACE_0420 values less than ('0501'),
partition T_IVRA_DAY_TRACE_0501 values less than ('0510'),
partition T_IVRA_DAY_TRACE_0510 values less than ('0520'),
partition T_IVRA_DAY_TRACE_0520 values less than ('0601'),
partition T_IVRA_DAY_TRACE_0601 values less than ('0610'),
partition T_IVRA_DAY_TRACE_0610 values less than ('0620'),
partition T_IVRA_DAY_TRACE_0620 values less than ('0701'),
partition T_IVRA_DAY_TRACE_0701 values less than ('0710'),
partition T_IVRA_DAY_TRACE_0710 values less than ('0720'),
partition T_IVRA_DAY_TRACE_0720 values less than ('0801'),
partition T_IVRA_DAY_TRACE_0801 values less than ('0810'),
partition T_IVRA_DAY_TRACE_0810 values less than ('0820'),
partition T_IVRA_DAY_TRACE_0820 values less than ('0901'),
partition T_IVRA_DAY_TRACE_0901 values less than ('0910'),
partition T_IVRA_DAY_TRACE_0910 values less than ('0920'),
partition T_IVRA_DAY_TRACE_0920 values less than ('1001'),
partition T_IVRA_DAY_TRACE_1001 values less than ('1010'),
partition T_IVRA_DAY_TRACE_1010 values less than ('1020'),
partition T_IVRA_DAY_TRACE_1020 values less than ('1101'),
partition T_IVRA_DAY_TRACE_1101 values less than ('1110'),
partition T_IVRA_DAY_TRACE_1110 values less than ('1120'),
partition T_IVRA_DAY_TRACE_1120 values less than ('1201'),
partition T_IVRA_DAY_TRACE_1201 values less than ('1210'),
partition T_IVRA_DAY_TRACE_1210 values less than ('1220'),
partition T_IVRA_DAY_TRACE_1220 values less than (MAXVALUE)
;
建表之后创建了两个分区索引。创建语句如下create index IX_IVRA_DAY_AUTOKEYTRACE_q on T_IVRA_DAY_AUTOKEYTRACE ( USERTYPE, CALLEENO, CITYCODE,OPERATETRACE, LOGDATE)
local
(
partition idx_type_101 tablespace TBS_CSP_CC_IDX,
partition idx_type_110 tablespace TBS_CSP_CC_IDX,
partition idx_type_120 tablespace TBS_CSP_CC_IDX,
partition idx_type_201 tablespace TBS_CSP_CC_IDX,
partition idx_type_210 tablespace TBS_CSP_CC_IDX,
partition idx_type_220 tablespace TBS_CSP_CC_IDX,
partition idx_type_301 tablespace TBS_CSP_CC_IDX,
partition idx_type_310 tablespace TBS_CSP_CC_IDX,
partition idx_type_320 tablespace TBS_CSP_CC_IDX,
partition idx_type_401 tablespace TBS_CSP_CC_IDX,
partition idx_type_410 tablespace TBS_CSP_CC_IDX,
partition idx_type_420 tablespace TBS_CSP_CC_IDX,
partition idx_type_501 tablespace TBS_CSP_CC_IDX,
partition idx_type_510 tablespace TBS_CSP_CC_IDX,
partition idx_type_520 tablespace TBS_CSP_CC_IDX,
partition idx_type_601 tablespace TBS_CSP_CC_IDX,
partition idx_type_610 tablespace TBS_CSP_CC_IDX,
partition idx_type_620 tablespace TBS_CSP_CC_IDX,
partition idx_type_701 tablespace TBS_CSP_CC_IDX,
partition idx_type_710 tablespace TBS_CSP_CC_IDX,
partition idx_type_720 tablespace TBS_CSP_CC_IDX,
partition idx_type_801 tablespace TBS_CSP_CC_IDX,
partition idx_type_810 tablespace TBS_CSP_CC_IDX,
partition idx_type_820 tablespace TBS_CSP_CC_IDX,
partition idx_type_901 tablespace TBS_CSP_CC_IDX,
partition idx_type_910 tablespace TBS_CSP_CC_IDX,
partition idx_type_920 tablespace TBS_CSP_CC_IDX,
partition idx_type_1001 tablespace TBS_CSP_CC_IDX,
partition idx_type_1010 tablespace TBS_CSP_CC_IDX,
partition idx_type_1020 tablespace TBS_CSP_CC_IDX,
partition idx_type_1101 tablespace TBS_CSP_CC_IDX,
partition idx_type_1110 tablespace TBS_CSP_CC_IDX,
partition idx_type_1120 tablespace TBS_CSP_CC_IDX,
partition idx_type_1201 tablespace TBS_CSP_CC_IDX,
partition idx_type_1210 tablespace TBS_CSP_CC_IDX,
partition idx_type_1220 tablespace TBS_CSP_CC_IDX
);create index IX_IVRA_DAY_AUTOKEYTRACE_RE on T_IVRA_DAY_AUTOKEYTRACE (CALLEENO, CITYCODE, USERGRADE, OPERATETRACE, OPERATELAYER, SUCCCOUNT, FAILCOUNT, LOGDATE)
local
(
partition idx_re_101 tablespace TBS_CSP_CC_IDX,
partition idx_re_110 tablespace TBS_CSP_CC_IDX,
partition idx_re_120 tablespace TBS_CSP_CC_IDX,
partition idx_re_201 tablespace TBS_CSP_CC_IDX,
partition idx_re_210 tablespace TBS_CSP_CC_IDX,
partition idx_re_220 tablespace TBS_CSP_CC_IDX,
partition idx_re_301 tablespace TBS_CSP_CC_IDX,
partition idx_re_310 tablespace TBS_CSP_CC_IDX,
partition idx_re_320 tablespace TBS_CSP_CC_IDX,
partition idx_re_401 tablespace TBS_CSP_CC_IDX,
partition idx_re_410 tablespace TBS_CSP_CC_IDX,
partition idx_re_420 tablespace TBS_CSP_CC_IDX,
partition idx_re_501 tablespace TBS_CSP_CC_IDX,
partition idx_re_510 tablespace TBS_CSP_CC_IDX,
partition idx_re_520 tablespace TBS_CSP_CC_IDX,
partition idx_re_601 tablespace TBS_CSP_CC_IDX,
partition idx_re_610 tablespace TBS_CSP_CC_IDX,
partition idx_re_620 tablespace TBS_CSP_CC_IDX,
partition idx_re_701 tablespace TBS_CSP_CC_IDX,
partition idx_re_710 tablespace TBS_CSP_CC_IDX,
partition idx_re_720 tablespace TBS_CSP_CC_IDX,
partition idx_re_801 tablespace TBS_CSP_CC_IDX,
partition idx_re_810 tablespace TBS_CSP_CC_IDX,
partition idx_re_820 tablespace TBS_CSP_CC_IDX,
partition idx_re_901 tablespace TBS_CSP_CC_IDX,
partition idx_re_910 tablespace TBS_CSP_CC_IDX,
partition idx_re_920 tablespace TBS_CSP_CC_IDX,
partition idx_re_1001 tablespace TBS_CSP_CC_IDX,
partition idx_re_1010 tablespace TBS_CSP_CC_IDX,
partition idx_re_1020 tablespace TBS_CSP_CC_IDX,
partition idx_re_1101 tablespace TBS_CSP_CC_IDX,
partition idx_re_1110 tablespace TBS_CSP_CC_IDX,
partition idx_re_1120 tablespace TBS_CSP_CC_IDX,
partition idx_re_1201 tablespace TBS_CSP_CC_IDX,
partition idx_re_1210 tablespace TBS_CSP_CC_IDX,
partition idx_re_1220 tablespace TBS_CSP_CC_IDX
);执行sql为
SELECT
--T.USERTYPE USERTYPE,
SUM(T.SUCCCOUNT)/10000 SUCCCOUNT,
SUM(T.FAILCOUNT)/10000 FAILCOUNT,
SUM(T.TOMANCOUNT)/10000 TOMANCOUNT,
SUM(T.REPLAYCOUNT)/10000 REPLAYCOUNT,
SUM(T.UPCLASSCOUNT)/10000 UPCLASSCOUNT,
SUM(T.UPTOPCOUNT)/10000 UPTOPCOUNT,
SUM(T.SUCCHANGUPCOUNT)/10000 SUCCHANGUPCOUNT,
SUM(T.FAILHANGUPCOUNT)/10000 FAILHANGUPCOUNT
FROM T_IVRA_DAY_AUTOKEYTRACE T
WHERE
T.USERTYPE = '0'
and T.CALLEENO = '1'
AND T.CITYCODE = '1'
-- AND T.USERTYPE IS NOT NULL
AND T.OPERATETRACE like '1' || '%'
AND T.LOGDATE >= TO_DATE('2009-08-01' ||' 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND T.LOGDATE <= TO_DATE('2009-08-31' ||' 23:59:59','YYYY-MM-DD HH24:MI:SS')
and TO_CHAR(T.LOGDATE,'HH24:MI:SS') >= '00:00:00'
and TO_CHAR(T.LOGDATE,'HH24:MI:SS') <= '23:59:59'
and t.partid >= '0801'
and t.partid <= '0820'
同样的代码，为什么在9i和11g上的执行计划会不一样呢？
图片不会上传啊...郁闷
11g使用CBO
所以执行计划不同。