本帖最后由 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=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 )ANDTESTCOL7_LEVEL in ( 4 , 6 , 5 , 1 , 7 , 8 , 3 , 2 , 33 , 31 , 32 , 0 )。所以还请各位大牛帮我分析分析，究竟导致查询缓慢的原因是什么，哪里需要优化的（暂不考虑select * 的优化），感激不尽！！！
|
