Oracle小于条件导致索引失效

基础索引建立

创建一般索引：Non-Unique

CREATE INDEX IDX_ORDER_TASK ON IC_GRANT_ORDER (ACCT_DATE ASC);

推荐这种，如果需要唯一，可以单加一个唯一约束，这样以后改成非唯一只需要去除约束即可。

创建唯一索引：Unique

CREATE UNIQUE INDEX IDX_ORDER_TASK ON IC_GRANT_ORDER (ACCT_DATE ASC);

如果后期要改成非唯一索引，需要删除索引，重新建立

对订单表的ACCT_DATE进行查询：

执行计划

TABLE ACCESS FULL：全表扫描
INDEX RANGE SCAN ：索引扫描
TABLE ACCESS BY INDEX ROWID：通过ROWID唯一索引查询
本次建立的是Non-Unique索引，底层会通过索引字段和ROWID组成联合索引，查询时会先查询索引字段，然后查询ROWID快速定位数据。

小于条件的执行计划： (小于等于执行计划与小于一样)

SQL:

explain plan for 
select id from IC_GRANT_ORDER where ACCT_DATE<'20200601' and send_status='04';
SELECT * from table(dbms_xplan.display);

执行计划:

Plan hash value: 3153622128
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     9 |   405 |   718   (1)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| IC_GRANT_ORDER |     9 |   405 |   718   (1)| 00:00:09 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SEND_STATUS"='04' AND "ACCT_DATE"<'20200601')
 

大于条件的执行计划： (大于等于执行计划与大于一样)

SQL:

explain plan for 
select id from IC_GRANT_ORDER where ACCT_DATE>'20200623' and send_status='04';
SELECT * from table(dbms_xplan.display);   

执行计划:

Plan hash value: 2309823823
 
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    45 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| IC_GRANT_ORDER |     1 |    45 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ORDER_TASK |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SEND_STATUS"='04')
   2 - access("ACCT_DATE">'20200623')

结论

sql中的非唯一索引字段判断，如果是包含小于条件，会导致索引失效。大于和等于正常走索引。
小于失效的原因：根据执行计划可知，底层会将非唯一索引与rowid合为联合索引，因此，范围无法使用索引。但是大于为何有效？目前还没有搞清楚