Oracle索引问题汇总

一、oracle 数据库TIMESTAMP 时间字段，设置索引后，通过该字段进行排序，索引排序不生效问题

  1. 记录下在工作中遇到的一次索引问题

     问题描述：

        数据库：oracle；

       日志记录表中的一个创建时间（create_time，字段类型timestamp）字段，在该字段设置了索引后，通过该字段进行排序时，索引排序并没有起作用。

     解决方式：

         将create_time字段类型修改为varchar2类型，格式为 yyyy-MM-dd HH24:mi:ss，再次排序时索引排序生效，效率也提高了不少。难道timestamp排序时索引不能使用吗？具体原因还需要进一步落实，等有时间在深入的了解。

二、oracle 时间条件值范围越大就不走索引问题解决

oracle 时间条件值范围越大就不走索引问题解决：使用强制索引
在写一个比较复杂的统计语句的时候，其中涉及到了时间的条件。但在执行测试过程中发现开始时间和结束时间的范围在两三天的时候执行计划里是走的索引，查询很快，当把时间范围扩大到五天、十天、一个月的时候执行计划里反而全表扫描了，查询效率慢了几十倍不止，这对于统计一个大表来说是致命的。
　　经过资料查询发现在oracle中有一个因素影响是进行全表扫描还是索引扫描，那就是查找的数据如果超过总数的20%左右，就会影响到扫描方式，不过这只是一个因素，不完全取决于它。这时候，如果对业务清晰，可以尝试使用强制索引，测试查询语句的性能。

使用强制索引，在SELECT 后面加上/…/ 中间加上索引的属性，代码如下：

SELECT /*+index(t pk_emp)*/* FROM EMP T
--强制索引，/*.....*/第一个星星后不能有空格，里边内容结构为：加号index(表名 空格 索引名)。
--如果表用了别名，注释里的表也要使用别名。

在使用了强制索引后发现日期跨度比较大的时候仍然用到了索引，查询速度由原来的一分钟提升到了1-2秒。

————————————————
原文链接：oracle 时间条件值范围越大就不走索引问题解决：使用强制索引_oracle日期大于小于走索引-CSDN博客

三、Oracle小于条件导致索引失效

ORACLE建索引的小发现
基础索引建立
创建一般索引：Non-Unique
创建唯一索引：Unique
对订单表的ACCT_DATE进行查询：
执行计划
小于条件的执行计划： (小于等于执行计划与小于一样)
SQL:
执行计划:
大于条件的执行计划： (大于等于执行计划与大于一样)
SQL:
执行计划:
结论
基础索引建立
创建一般索引：Non-Unique
CREATE INDEX IDX_ORDER_TASK ON IC_GRANT_ORDER (ACCT_DATE ASC);
1
推荐这种，如果需要唯一，可以单加一个唯一约束，这样以后改成非唯一只需要去除约束即可。

创建唯一索引：Unique
CREATE UNIQUE INDEX IDX_ORDER_TASK ON IC_GRANT_ORDER (ACCT_DATE ASC);
1
如果后期要改成非唯一索引，需要删除索引，重新建立

对订单表的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);
1
2
3
执行计划:
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')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
大于条件的执行计划： (大于等于执行计划与大于一样)
SQL:
explain plan for
select id from IC_GRANT_ORDER where ACCT_DATE>'20200623' and send_status='04';
SELECT * from table(dbms_xplan.display);
1
2
3
执行计划:
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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
结论
sql中的非唯一索引字段判断，如果是包含小于条件，会导致索引失效。大于和等于正常走索引。
小于失效的原因：根据执行计划可知，底层会将非唯一索引与rowid合为联合索引，因此，范围无法使用索引。但是大于为何有效？目前还没有搞清楚
————————————————
原文链接：Oracle小于条件导致索引失效_oracle小于等于走索引吗-CSDN博客