常见的Oracle索引抑制场景

索引的抑制是在WHERE语句涉及到索引列的访问时却进行了table full scan等高I/O的方式扫描表。这是编写SQL的过程中经常遇到的问题,很多trap会导致索引无法使用。下面将一一列举常见的索引抑制场景。

1. 使用不等运算符(<>, !=)

索引键只包含表中已经存在的非NULL数据。当在WHERE子句中使用不等运算符(<>, !=)时。该列的索引将不能使用。

2. 使用IS NULL或IS NOT NULL

当WHERE子句使用IS NULL或者IS NOT NULL的时候,因为B树索引的索引键不包含NULL的值,索引不会被使用。
可以禁用NULL值,即在表中创建/修改表时使用NOT NULL约束(可以配合列default一起使用)。但如果表中该列存在NULL数据时,NOT NULL约束创建会失败:

–当我们将所有NULL值修改后,创建会成功。

在创建表时对列指定NOT NULL或default属性,以避免可能出现的性能问题。

3. 使用LIKE

LIKE常见的用法是LIKE ‘%someword%’
在不涉及回表的情况下,条件中有LIKE关键字会使用索引。而大部分情况下,LIKE则不会使用索引。

可以看到,当%在值前面的时候,索引不会被使用。但当值在%前面的时候,Oracle可以使用索引。如果当%在值前面的场景下使用索引时,需要使用全文索引。

4. 对列使用函数或表达式

在SQL语句的where子句中对存在的索引列使用了函数或者表达式时,优化器会忽略普通的B树索引。只有使用函数(表达式)索引可以解决这个问题。
例如下面的场景:
select empno, ename from emp where trunc(hiredate) = ’01-MAY-01′;
改成下面的SQL就可以正常使用B树索引进行查找:
select empno, ename from emp where hiredate > ’01-MAY-01′ and hiredate < (TO_DATE('01-MAY-01') + 0.9999 ); 使用函数或者表达式索引有另一个陷阱——在函数代码变更后需要重建函数索引,否则Oracle将返回错误的输出结果而不给提示。

-- 在重建索引之前,索引键里记录的是之前函数计算得到的值,所以这里查询是失败的。

-- 重建之后得到了正确的值

5. 比较不匹配的数据类型

在一些情况下,需要用varchar2类型来保存number类型(例如number的前置位需要’0’等必要的信息),那么这时候在使用这些列的时候,隐式的转换就会发生从而抑制了索引的使用。

上面字符串->数字的转换,Oracle自动对列加上了隐式的TO_NUMBER()函数,从而进行了TAF。而对于数字->字符串的转换,隐式的函数转换却得到了避免,由Oracle直接进行处理:

6. 排序的索引使用场景

一般情况的索引是可以消除排序的,但如果排序涉及到一个是部分升序部分降序,就必须建立对应部分升降序的索引,否则无法用这个来消除排序。

采用了正确的排序方式创建索引后,就可以使用索引来避免sort(memory),Cost大大降低了。
这里需要注意一种情况,当使用索引逆序的顺序来排序时,该查询依然可以使用索引的INDEX FULL SCAN DESCENDING来避免排序。

7. 同时使用MIN(col)和MAX(col)

这是一个非常特殊的情况,对象在扫描的时候不能进行二次扫描。一般情况下使用MAX/MIN的时候进行了INDEX FULL SCAN (MIN/MAX),这种扫描直接查找索引的最左/最右叶节点,速度非常快。而同时使用MIN(col)和MAX(col)虽然没有抑制索引的使用,但是却进行了index fast full scan。上面得到的197个一致性读远远大于下面的语句:

^^

参考:
梁敬斌 《基于案例的SQL优化》
Richard Niemiec 《Oracle Database 11gR2 Performance Tuning Tips & Techniques》

Posted in Oracle.