索引抑制是一些没有经验的开发人员经常犯的错误之一。在oracle中有很多陷阱会使一些线索无法使用。
oracle优化器在后台工作,选择并使用可能最有效的方法来返回数据。例如,在许多情况下不需要指定where
子句oracle就可以使用索引。具体来说,如果查询索引列的min和max值,oracle从索引中就可以得到结果,而不
需要去访问表;类似的,如果对索引列执行count函数,oracle可以使用索引而不是列中实际的数据。在下面的内容
中,您将看到where子句的逻辑阻止oracle使用索引的情况。
1.使用运算符<>和!=
索引只能用于查找表中已存在的数据。如下例:
SQL> SET AUTOTRACE ON EXPLAIN
set linesize 1000
SQL> select empno, ename from emp where empno <> 7900;
EMPNO ENAME
---------- ------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ------------------------------
7902 FORD
7934 MILLER
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 130 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"<>7900)
SQL>
SQL> select table_name, index_name, column_name, column_position from user_ind_columns
2 where table_name='EMP'
3 order by table_name, index_name, column_position
4 ;
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
EMP EMP_ID2 SAL 1
EMP PK_EMP EMPNO 1
SQL>
由上可见empno列是有索引pk_emp的,但却没有使用,我们换成=试下
SQL> select empno, ename from emp where empno=7900;
EMPNO ENAME
---------- ------------------------------
7900 JAMES
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
SQL>
2.使用is null 或者is not null
因为null值没有被定义,所以索引的使用会被抑制。数据库中没有值等于null,甚至null值也不等于null
如果被索引的列在某些行中存在null值,在索引中就不会有相应的条目(除非使用位图索引,这是位图索引对于null搜索通常很快的原因)
一般情况下,即使sal列上有索引,下面的语句也会引起全表扫描
SQL> select empno, ename,deptno from emp
2 where sal is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL" IS NULL)
SQL> select empno, ename, deptno
2 from emp
3 where sal=7900;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1629720216
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL"=7900)
SQL>
3. 使用like
在某些情况下,条件中有LIKE关键字会使用索引,而在某些情况下则不会。如like '%somevalue%'不会使用索引,但like 'somevalue%'可以使用索引
4.函数
除非使用基于函数的索引,否则在SQL语句的where子句中对存在索引的列使用函数时,优化器会忽略掉这些索引。一些常见的函数,如TRUNC, SUBSTR,
TO_DATE,TO_CHAR和INSTR等,都可以改变列的值;因此,被引用的列上的索引将无法使用,例如:下面的语句会执行一次全表扫描,即使hiredate列上
存在索引(只要它不是基于函数的索引):
SQL> create index hire_date_idx on emp(hiredate);
Index created
SQL>
SQL> select table_name, index_name, column_name, column_position from user_ind_columns
2 where table_name='EMP'
3 order by table_name, index_name, column_position;
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
EMP EMP_ID2 SAL 1
EMP HIRE_DATE_IDX HIREDATE 1
EMP PK_EMP EMPNO 1
SQL>
SQL> select empno , ename, deptno
2 from emp
3 where trunc(hiredate)='01-MAY-01';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 21 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("HIREDATE"))='01-MAY-01')
SQL>
把上面的语句改成下面的样子就可以通过索引查找
SQL> select empno, ename, deptno
2 from emp
3 where hiredate > '01-MAY-01'
4 and hiredate < (to_date('01-MAY-01') + 0.99999);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3652182612
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 21 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HIRE_DATE_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter('01-MAY-01'<TO_DATE('01-MAY-01')+.99998842592592592592592592592592592592
59)
3 - access("HIREDATE">'01-MAY-01' AND "HIREDATE"<TO_DATE('01-MAY-01')+.999988425925
9259259259259259259259259259)
SQL>
注:通过改变所比较列上的值,而不用改变列本身,就可以启用索引。这样可以避免全表扫描。
5.比较不匹配的数据类型
一种很难解决的性能问题是比较不匹配的数据类型,oracle不但会对那些不兼容的数据类型报错,反而会做隐式地转换。
例如,oracle可以隐式地转换varchar2类型的列中的数据去匹配类型数据。
下例中的account_number列就是VARCHAR2类型。下面的语句就会执行全表扫描,即使account_number列上有索引:
select bank_name, address, city, state, zip
from banks
where account_number=990354;
oracle自动把where子句变成:to_number(account_number)=990354
这个查询仅可以通过“全表扫描”访问这个表(对编程人员来说通常都觉得很迷惑)。如此情况可能很少见,但在很多系统中,数字类型值可能会被
用0填充,然后指定为varchar2类型。像下面这样改写前面的语句,为值加上单引号,就可以使用account_number列上的索引:
select bank_name, address, city, state, zip
from banks
where account_number = '000990354';
另外一种可选方法是将account_number 列定义成number数据类型,这样做的前提是前置的0不是该列的关键信息。