这里使用表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
