1、场景
我这里正常情况下一天将近两千万的数据需要存储，现在存储在Oracle 11g数据库中，目前数据量达到1个多亿
2、表现问题
查询速度特别慢，查询语句如下
表结构（表经过转化）
CREATE TABLE MI_TEST_NUM
(
ID VARCHAR2(64) NOT NULL ENABLE,
PASSINGTIME TIMESTAMP (6) DEFAULT NULL,
CREATETIME TIMESTAMP (6) DEFAULT NULL,
FLAG VARCHAR2(32) DEFAULT NULL,
VEHICLETYPE VARCHAR2(30) DEFAULT null
) PARTITION BY RANGE (CREATETIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition part_t01 values less than(to_date('2020-06-14', 'YYYY-MM-DD')));
-- 创建索引
create index MITESTNUM_PASSINGTIME_localidx on MI_TEST_NUM(PASSINGTIME) LOCAL tablespace USERS;
-- 其他索引不再展示
-- 执行查询计划
explain plan FOR
SELECT t.*
FROM MI_TEST_NUM t
WHERE t.FLAG = 'H'
AND t.PASSINGTIME BETWEEN to_date('2020-06-10 00:00:00','yyyy--mm-dd hh24:mi:ss')
AND to_date('2020-06-21 23:59:59','yyyy--mm-dd hh24:mi:ss')
ORDER BY t.PASSINGTIME DESC
-- 查看执行计划
select * from table(dbms_xplan.display)
执行结果如下：
Plan hash value: 2713456464
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2258K| 629M| | 167K (1)| 00:33:35 | | |
| 1 | SORT ORDER BY | | 2258K| 629M| 705M| 167K (1)| 00:33:35 | | |
|* 2 | FILTER | | | | | | | | |
| 3 | PARTITION RANGE ALL| | 2258K| 629M| | 26024 (2)| 00:05:13 | 1 |1048575|
|* 4 | TABLE ACCESS FULL |MI_TEST_NUM| 2258K| 629M| | 26024 (2)| 00:05:13 | 1 |1048575|
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('2020-06-10 00:00:00','yyyy--mm-dd hh24:mi:ss')<=TO_DATE('2020-06-21
23:59:59','yyyy--mm-dd hh24:mi:ss'))
4 - filter("T"."PASSINGTIME">=TO_DATE('2020-06-10 00:00:00','yyyy--mm-dd hh24:mi:ss') AND
"T"."FLAG"='H' AND "T"."PASSINGTIME"<=TO_DATE('2020-06-21 23:59:59','yyyy--mm-dd hh24:mi:ss'))
以上结果可以看出 TABLE ACCESS FULL 没有用索引，而是全表扫描
3、尝试解决过程
经过查询，有人说，当查询的结果大于总数的30%，Oracle就会放弃使用索引，我就尝试查1、询2个小时的数据，确实查询很快
SELECT * FROM MI_TEST_NUM T
WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')
and PASSINGTIME<=to_date('2020-06-14 02:59:59','yyyy--mm-dd hh24:mi:ss')
ORDER BY PASSINGTIME DESC
用时间：10ms
查询数据：181236条
总数据量：119666551条
查询数据/总数据量：0.0015145084276725
索引使用：使用索引
使用执行计划explain plan FOR 查看是使用索引的
2、询24个小时的数据，确实查询很慢
SELECT * FROM MI_TEST_NUM T
WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')
and PASSINGTIME<=to_date('2020-06-14 23:59:59','yyyy--mm-dd hh24:mi:ss')
ORDER BY PASSINGTIME DESC
用时间：1m37s
查询数据：6725049条
总数据量：119666551条
查询数据/总数据量：0.0561982353782387
索引使用：不使用索引
3、询24个小时的数据，强制使用索引，确实查询很慢
SELECT /*+index(T MITESTNUM_PASSINGTIME_localidx)*/ * FROM MI_TEST_NUM T
WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')
and PASSINGTIME<=to_date('2020-06-16 23:59:59','yyyy--mm-dd hh24:mi:ss')
ORDER BY PASSINGTIME DESC
用时间：1m33s
查询数据：6725049条
总数据量：119666551条
查询数据/总数据量：0.0561982353782387
索引使用：使用索引
4、解决方案（分页查询）
Oracle不像MySQL查询时会自动给你分页，Oracle查询比如有2千万，他会去查询2千万的数据不会分页，需要自己分页查询
比如我查询前20条
SELECT ROW_.*, ROWNUM ROWNUM_
FROM (
SELECT * FROM MI_TEST_NUM T
WHERE PASSINGTIME >=to_date('2020-06-14 00:00:00','yyyy--mm-dd hh24:mi:ss')
and PASSINGTIME<=to_date('2020-06-14 02:59:59','yyyy--mm-dd hh24:mi:ss')
) ROW_
WHERE ROWNUM <= 20
