分区索引问题
分区索引问题
select * from emp_income_tax_temp where code='' and bmonth='' and id_card=''
以上语句本人作了分区处理同时也建了索引,但是本人发现上面的语句只用到了一个分区索引,但我改为普通表,同时建了索引却都使用了己建的索引这是为什么?
请高人指点,先谢了
---------------------------------
CREATE TABLE "DB_WWSB"."EMP_INCOME_TAX_TEMP"
( "ID_CARD" VARCHAR2(18),
"EMP_NAME" VARCHAR2(30),
"CODE" VARCHAR2(30),
"COMP_NAME" VARCHAR2(100),
"BMONTH" CHAR(6),
"OP_CODE" VARCHAR2(20),
"OP_TYPE" CHAR(1),
"TAX_ITEM" VARCHAR2(10),
"VDATA" VARCHAR2(4000)
)
PARTITION BY RANGE(BMONTH)
(
PARTITION jan2007 VALUES LESS THAN('200702'),
PARTITION feb2007 VALUES LESS THAN('200703'),
PARTITION mar2007 VALUES LESS THAN('200704'),
PARTITION apr2007 VALUES LESS THAN('200705'),
PARTITION may2007 VALUES LESS THAN('200706'),
PARTITION jun2007 VALUES LESS THAN('200707'),
PARTITION jul2007 VALUES LESS THAN('200708'),
PARTITION aug2007 VALUES LESS THAN('200709'),
PARTITION sep2007 VALUES LESS THAN('200710'),
PARTITION oct2007 VALUES LESS THAN('200711'),
PARTITION nov2007 VALUES LESS THAN('200712'),
PARTITION dec2007 VALUES LESS THAN('200801'),
PARTITION jan2008 VALUES LESS THAN('200802'),
PARTITION feb2008 VALUES LESS THAN('200803'),
PARTITION mar2008 VALUES LESS THAN('200804'),
PARTITION apr2008 VALUES LESS THAN('200805'),
PARTITION may2008 VALUES LESS THAN('200806'),
PARTITION jun2008 VALUES LESS THAN('200807'),
PARTITION jul2008 VALUES LESS THAN('200808'),
PARTITION aug2008 VALUES LESS THAN('200809'),
PARTITION sep2008 VALUES LESS THAN('200810'),
PARTITION oct2008 VALUES LESS THAN('200811'),
PARTITION nov2008 VALUES LESS THAN('200812'),
PARTITION dec2008 VALUES LESS THAN('200901')
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_WWSB_DATA" ;
---------------------EMP_INCOME_TAX_TEMP表index处理-------------
CREATE INDEX par_locd_IDX_EIT_BMONTHcur
ON emp_income_tax_temp(BMONTH)
local
(
partition jan2007 tablespace TS_WWSB_DATA,
partition feb2007 tablespace TS_WWSB_DATA,
partition mar2007 tablespace TS_WWSB_DATA,
partition apr2007 tablespace TS_WWSB_DATA,
partition may2007 tablespace TS_WWSB_DATA,
partition jun2007 tablespace TS_WWSB_DATA,
partition jul2007 tablespace TS_WWSB_DATA,
partition aug2007 tablespace TS_WWSB_DATA,
partition sep2007 tablespace TS_WWSB_DATA,
partition oct2007 tablespace TS_WWSB_DATA,
partition nov2007 tablespace TS_WWSB_DATA,
partition dec2007 tablespace TS_WWSB_DATA ,
partition jan2008 tablespace TS_WWSB_DATA,
partition feb2008 tablespace TS_WWSB_DATA,
partition mar2008 tablespace TS_WWSB_DATA,
partition apr2008 tablespace TS_WWSB_DATA,
partition may2008 tablespace TS_WWSB_DATA,
partition jun2008 tablespace TS_WWSB_DATA,
partition jul2008 tablespace TS_WWSB_DATA,
partition aug2008 tablespace TS_WWSB_DATA,
partition sep2008 tablespace TS_WWSB_DATA,
partition oct2008 tablespace TS_WWSB_DATA,
partition nov2008 tablespace TS_WWSB_DATA,
partition dec2008 tablespace TS_WWSB_DATA
)
------------------------------------------------------------
CREATE INDEX par_locd_IDX_EIT_IDCARDcur
ON emp_income_tax_temp(ID_CARD)
local
(
partition jan2007 tablespace TS_WWSB_DATA,
partition feb2007 tablespace TS_WWSB_DATA,
partition mar2007 tablespace TS_WWSB_DATA,
partition apr2007 tablespace TS_WWSB_DATA,
partition may2007 tablespace TS_WWSB_DATA,
partition jun2007 tablespace TS_WWSB_DATA,
partition jul2007 tablespace TS_WWSB_DATA,
partition aug2007 tablespace TS_WWSB_DATA,
partition sep2007 tablespace TS_WWSB_DATA,
partition oct2007 tablespace TS_WWSB_DATA,
partition nov2007 tablespace TS_WWSB_DATA,
partition dec2007 tablespace TS_WWSB_DATA ,
partition jan2008 tablespace TS_WWSB_DATA,
partition feb2008 tablespace TS_WWSB_DATA,
partition mar2008 tablespace TS_WWSB_DATA,
partition apr2008 tablespace TS_WWSB_DATA,
partition may2008 tablespace TS_WWSB_DATA,
partition jun2008 tablespace TS_WWSB_DATA,
partition jul2008 tablespace TS_WWSB_DATA,
partition aug2008 tablespace TS_WWSB_DATA,
partition sep2008 tablespace TS_WWSB_DATA,
partition oct2008 tablespace TS_WWSB_DATA,
partition nov2008 tablespace TS_WWSB_DATA,
partition dec2008 tablespace TS_WWSB_DATA
)
;
--------------------------------------------------------------
CREATE INDEX par_locd_IDX_EITT_CODEcur
ON emp_income_tax_temp(CODE)
local
(
partition jan2007 tablespace TS_WWSB_DATA,
partition feb2007 tablespace TS_WWSB_DATA,
partition mar2007 tablespace TS_WWSB_DATA,
partition apr2007 tablespace TS_WWSB_DATA,
partition may2007 tablespace TS_WWSB_DATA,
partition jun2007 tablespace TS_WWSB_DATA,
partition jul2007 tablespace TS_WWSB_DATA,
partition aug2007 tablespace TS_WWSB_DATA,
partition sep2007 tablespace TS_WWSB_DATA,
partition oct2007 tablespace TS_WWSB_DATA,
partition nov2007 tablespace TS_WWSB_DATA,
partition dec2007 tablespace TS_WWSB_DATA ,
partition jan2008 tablespace TS_WWSB_DATA,
partition feb2008 tablespace TS_WWSB_DATA,
partition mar2008 tablespace TS_WWSB_DATA,
partition apr2008 tablespace TS_WWSB_DATA,
partition may2008 tablespace TS_WWSB_DATA,
partition jun2008 tablespace TS_WWSB_DATA,
partition jul2008 tablespace TS_WWSB_DATA,
partition aug2008 tablespace TS_WWSB_DATA,
partition sep2008 tablespace TS_WWSB_DATA,
partition oct2008 tablespace TS_WWSB_DATA,
partition nov2008 tablespace TS_WWSB_DATA,
partition dec2008 tablespace TS_WWSB_DATA
)
---------------------------------------
我用PL/SQL developer 按f5执行后是以下结果
Description Object owner Object name Cost=1 Cardinality=1 Bytes=2129
TABLE ACCESS BY LOCAL INDEX ROWID Object owner=DB_WWSB Object name=EMP_INCOME_TAX_TEMP Cost=1
Cardinality=1 Bytes=2129
INDEX RANGE SCAN Object owner=DB_WWSB Object name=PAR_LOCD_IDX_EITT_CODECUR Cost=1 Cardinality=1