索引抑制是一些没有经验的开发人员经常犯的错误之一。在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不是该列的关键信息。
2155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
