-- 創建表
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 | JH_CAR_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'))
問題是，我這個查詢，為什么不走索引？
uj5u.com熱心網友回復：
可能有多種情況吧，先查看一下索引是否失效，也有可能系統認為你的陳述句不走索引更合適。你用hint強制走索引試試
uj5u.com熱心網友回復：
知道了，因為資料量大，沒有分頁，分頁查詢就ok了uj5u.com熱心網友回復：
既然查詢條件是PASSINGTIME，你的磁區表為啥不按PASSINGTIME這個欄位做磁區鍵呢？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/9995.html
標籤：開發