This post the last edited by woaini7374 dough in 2016-12-5


I just contact with Oracle data soon, now encountered a partition the problem of low query has a TEST table, the table one day create a partition, the partitioning key RECORD_DATE, is to use the DATE type is considering the ease of use, the structure of the table as shown below (between some reason, the table name and field playing other characters instead of) :
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)
)
In tablespace TS_TEST
Partition by range (RECORD_DATE)
The interval (1)
(partition
P0
Values less than (20161001));

The create index IDX_TEST_TIME on TEST (
RECORD_DATE ASC,
RECORD_DATE ASC
)
The local
In tablespace TS_IDX_TEST;

The create index IDX_TEST on TEST (
RECORD_DATE ASC,
TESTCOL2_ID ASC,
TESTCOL_TYPE ASC,
TESTCOL7_LEVEL ASC
)
The local
In tablespace TS_IDX_TEST;

The create index IDX_TEST2 on TEST (
TESTCOL_ID ASC
)
The local
In tablespace TS_IDX_TEST;
At present there are more than 100000 data table, if I use the select * from TEST the where (RECORD_DATE=20161203 and RECORD_TIME & gt;=131140847 and RECORD_TIME & lt;=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 & gt; 20161103 and RECORD_DATE & lt;=20161202) or (RECORD_DATE=20161203 and RECORD_TIME & lt;=142212378)) AND TESTCOL2_ID,96,97,98 (95) AND in TESTCOL_TYPE in (51, 52, 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), so also please god help me analysis analysis, what is the cause of slow queries, where need optimization (not considering the optimization of select *), thank you very much!!!!!!

CodePudding user response:

Look at the execution plan

CodePudding user response:

To carry out the first SQL plan: PARTITION RANGE SINGLE
TABLE ACCESS BY LOCAL INDEX ROWID
The INDEX RANGE SCAN

CodePudding user response:

Article 3.8 W data were found out, not slow; After all, the Internet also want time, displayed to time;

CodePudding user response:

That still need to optimize?

CodePudding user response:

Indexing try

CodePudding user response:

Use in other ways instead