本帖最后由 woaini7374 于 2016-12-5 12:39 编辑
本人刚接触Oracle数据不久,现在遇到一个分区查询慢的问题,有一张TEST表,表一天建立一个分区,分区键RECORD_DATE ,之所以没有用DATE类型是考虑到使用的方便性,表的结构如下所示(介于某种原因,表名和字段暂用其他字符代替):
create table TEST (
TESTCOL_ID VARCHAR2(128) not null,
TESTCOL_SEQ NUMBER(3) not null,
TESTCOL_TYPE NUMBER(5),
RECORD_DATE NUMBER(10) not null,
RECORD_TIME NUMBER(10) not null,
TESTCOL2_ID NUMBER(5),
TESTCOL2_NAME VARCHAR2(64),
TESTCOL3_ID NUMBER(10),
TESTCOL3_NAME VARCHAR2(64),
TESTCOL4_ID NUMBER(5),
TESTCOL4_NAME VARCHAR2(64),
TESTCOL5_MARK NUMBER(3),
TESTCOL5_DATE NUMBER(10),
TESTCOL5_TIME NUMBER(10),
TESTCOL6_TYPE NUMBER(3),
TESTCOL6_ID NUMBER(10),
TESTCOL6_NAME VARCHAR2(64),
TESTCOL_DESC VARCHAR2(4000),
RESET NUMBER(3),
TESTCOL7_LEVEL NUMBER(3)
)
tablespace TS_TEST
partition by range (RECORD_DATE)
interval(1)
(partition
P0
values less than (20161001));create index IDX_TEST_TIME on TEST (
RECORD_DATE ASC,
RECORD_DATE ASC
)
local
tablespace TS_IDX_TEST;create index IDX_TEST on TEST (
RECORD_DATE ASC,
TESTCOL2_ID ASC,
TESTCOL_TYPE ASC,
TESTCOL7_LEVEL ASC
)
local
tablespace TS_IDX_TEST;create index IDX_TEST2 on TEST (
TESTCOL_ID ASC
)
local
tablespace TS_IDX_TEST;
目前表里有10万多的数据。如果我用select * from TEST where ( RECORD_DATE=20161203 and RECORD_TIME>=131140847 and RECORD_TIME<=161140847 ) AND TESTCOL2_ID in ( 95,96,97,98 ) AND TESTCOL_TYPE in ( 52 , 51 , 12 , 31 , 32 , 33 , 34 , 35 , 36 , 53 , 44 , 43 , 16 , 14 , 15 , 17 , 99 , 11 , 19 , 62 , 63 , 61 , 42 , 41 , 18 , 103 , 125 , 121 , 120 , 102 , 128 , 122 , 127 , 100 , 101 , 104 , 140 , 110 , 126 , 123 , 129 , 124 ) AND TESTCOL7_LEVEL in ( 4 , 6 , 5 , 1 , 7 , 8 , 3 , 2 , 33 , 31 , 32 , 0 ) ,查询用时大概需要3秒多,检索出来的数据3万8千多条,感觉这已经很慢了,这还是不垮分区查,如果跨分区查询。更加慢,比如执行:select * from TEST where ( (RECORD_DATE=20161103 and RECORD_TIME>=142212378) or (RECORD_DATE>20161103 and RECORD_DATE<=20161202) or (RECORD_DATE=20161203 and RECORD_TIME<=142212378) ) AND TESTCOL2_ID in ( 95,96,97,98 ) AND TESTCOL_TYPE in ( 52 , 51 , 12 , 31 , 32 , 33 , 34 , 35 , 36 , 53 , 44 , 43 , 16 , 14 , 15 , 17 , 99 , 11 , 19 , 62 , 63 , 61 , 42 , 41 , 18 , 103 , 125 , 121 , 120 , 102 , 128 , 122 , 127 , 100 , 101 , 104 , 140 , 110 , 126 , 123 , 129 , 124 ) AND TESTCOL7_LEVEL in ( 4 , 6 , 5 , 1 , 7 , 8 , 3 , 2 , 33 , 31 , 32 , 0 )。所以还请各位大神帮我分析分析,究竟导致查询缓慢的原因是什么,哪里需要优化的(暂不考虑select * 的优化),感激不尽!!!
TABLE ACCESS BY LOCAL INDEX ROWID
INDEX RANGE SCAN