索引不被使用的几种情况

这里使用表scott.emp 来做实验，除了原来empno 上面的primary key 之外，在comm、mgr、job 三个列上面也建立了索引：

SQL> select table_name, index_name, column_name from user_ind_columns where table_name='EMP';

TABLE_NAME      INDEX_NAME      COLUMN_NAME

--------------- --------------- -----------------------------------------------------------------------------

EMP              PK_EMP           EMPNO

EMP              COMM_TST        COMM

EMP              MGR_TST          MGR

EMP              JOB_TST          JOB

SQL> DESC EMP

Name       Null?          Type

----------------------------------------- -------- ----------------------------

EMPNO      NOT NULL       NUMBER(4)

ENAME                     VARCHAR2(10)

JOB                       VARCHAR2(9)

MGR                       NUMBER(4)

HIREDATE                  DATE

SAL                       NUMBER(7,2)

COMM                      NUMBER(7,2)

DEPTNO                    NUMBER(2)

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- -------------------------------------------------------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81  1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250  500 30

7566 JONES MANAGER 7839 02-APR-81  2975 20

7654 MARTIN SALESMAN  7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81  2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000  10

7844 TURNER SALESMAN 7698 08-SEP-81  1500  0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81  950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300  10

14 rows selected.

SQL> set autotrace on;

（1）当对同一个表中的两个列（empno 和mgr）进行比较的情形下，索引（pk_emp 和mgr_tst）有时不会被使用：

SQL> select * from emp where empno<mgr;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=8 Bytes=296)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=8 Bytes=296)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1043 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

7 rows processed

（2）Null 值。一般情形下，索引中并不存在Null 值。如果where 语句中出现is null 或者is notnull 时，索引就不能被使用（comm._tst 没有被使用）。

SQL> select * from emp where comm is not null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=4 Bytes=148)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=4 Bytes=148)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

902 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

（3）当where 语句中存在有not function 时，比如not in、not exist、column <> value、column1> value 或column2 < value 等情形下，索引不能被使用。

SQL> select * from emp where comm <> 1000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=111)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Bytes=111)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

6 physical reads

0 redo size

902 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

（4）当使用了single-row function 时，如nvl、to_char、lower 等，索引不能被使用。

SQL> select ename, nvl(comm, 0) from emp;

ENAME NVL(COMM,0)

---------- -----------

SMITH 0

ALLEN 300

WARD 500

JONES 0

MARTIN 1400

BLAKE 0

CLARK 0

SCOTT 0

KING 0

TURNER 0

ADAMS 0

JAMES 0

FORD 0

MILLER 0

14 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=112)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=112)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

623 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

（5）当使用通配符号%或者_作为查询字符串的第一个字符时，例如，在语句“where namelike ‘%xxxx’”的情形下，索引也无法使用（对于这种情况，现在很多数据库都支持所谓的“全文检索索引”，可以很好地解决这个问题）。但是如果查询字符串的第一个字确定，例如“where namelike ‘a%’”这样，则可以使用索引。

SQL> select ename, job from emp where job like '%C%';

ENAME JOB

---------- ---------

SMITH CLERK

ADAMS CLERK

JAMES CLERK

MILLER CLERK

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=14)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=14)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

497 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed

SQL> select ename, job from emp where job like 'C%';

ENAME JOB

---------- ---------

SMITH CLERK

ADAMS CLERK

JAMES CLERK

MILLER CLERK

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=42)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=3 Bytes=42)

2 1 INDEX (RANGE SCAN) OF 'JOB_TST' (INDEX) (Cost=1 Card=3)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

1 physical reads

0 redo size

497 bytes sent via SQL*Net to client

500 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

4 rows processed