使用索引的误区之一：没有使用复合索引的前导列导致查询不使用索引

在 oracle 中，我们经常以为建立了索引， sql 查询的时候就会如我们所希望的那样使用索引，事实上， oracle 只会在一定条件下使用索引，这里我们总结数第一点： oracle 会在条件中包含了前导列时使用索引，即查询条件中必须使用索引中的第一个列，请看下面的例子

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

DUMMY TABLE

EMP TABLE

SALGRADE TABLE

** 建立一个联合索引（注意复合索引的索引列顺序） **

SQL> create index emp_id1 on emp( empno,ename,deptno );

Index created

** 建立一个单键索引 **

SQL> create index emp_id2 on emp(sal);

Index created

SQL> select table_name,index_name from user_indexes

2 where table_name='EMP';

TABLE_NAME INDEX_NAME

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

EMP EMP_ID1

EMP EMP_ID2

SQL> SELECT * FROM USER_IND_COLUMNS

2 /

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND

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

EMP_ID1 EMP EMPNO 1 22 0 ASC

EMP_ID1 EMP ENAME 2 10 10 ASC

EMP_ID1 EMP DEPTNO 3 22 0 ASC

EMP_ID2 EMP SAL 1 22 0 ASC

** 下面的查询由于没有使用到复合索引的前导列，所以没有使用索引 **

select job, empno from emp where ename='RICH';

PLAN_TABLE_OUTPUT

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

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

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

| 0 | SELECT STATEMENT | | | | |

|* 1 | TABLE ACCESS FULL | EMP | | | |

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

Predicate Information (identified by operation id):

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

1 - filter("EMP"."ENAME"='RICH')

Note: rule based optimization

14 rows selected

** 下面的查询也由于没有使用到复合索引的前导列，所以没有使用索引 **

select job, empno from emp where deptno=30;

PLAN_TABLE_OUTPUT

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

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

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

| 0 | SELECT STATEMENT | | | | |

|* 1 | TABLE ACCESS FULL | EMP | | | |

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

Predicate Information (identified by operation id):

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

1 - filter("EMP"."DEPTNO"=30)

Note: rule based optimization

14 rows selected

** 下面的查询使用了复合索引中的前导列，所以查询走索引了 **

select job, empno from emp where empno=7777;

PLAN_TABLE_OUTPUT

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

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

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

| 0 | SELECT STATEMENT | | | | |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | |

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

Predicate Information (identified by operation id):

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

2 - access("EMP"."EMPNO"=7777)

Note: rule based optimization

15 rows selected

** 下面的查询使用了复合索引中的第一列和第二列，所以查询走索引了 **

select job, empno from emp where empno=7777 and ename='RICH';

PLAN_TABLE_OUTPUT

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

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

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

| 0 | SELECT STATEMENT | | | | |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | |

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

Predicate Information (identified by operation id):

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

2 - access("EMP"."EMPNO"=7777 AND "EMP"."ENAME"='RICH')

Note: rule based optimization

15 rows selected

** 使用了复合索引的全部列，所以走索引了，另外由于选了了索引中没有包含的列（ job ）， **

** 所以进行索引全表扫描得到满足条件的 rowid 后，还要到表中检索相应的行 **

select job, empno from emp where empno=7777 and ename='RICH' and deptno=30;

PLAN_TABLE_OUTPUT

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

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

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

| 0 | SELECT STATEMENT | | | | |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | |

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

Predicate Information (identified by operation id):

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

2 - access("EMP"."EMPNO"=7777 AND "EMP"."ENAME"='RICH' AND "EMP"."DEP

TNO"=30)

Note: rule based optimization

16 rows selected

** 使用了复合索引的全部列，所以走索引了，而且由于所有选择的列都包含在索引中，所以仅仅进行了索引范围扫描 **

select empno from emp where empno=7777 and ename='RICH' and deptno=30;

PLAN_TABLE_OUTPUT

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

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

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

| 0 | SELECT STATEMENT | | | | |

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

Predicate Information (identified by operation id):

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

1 - access("EMP"."EMPNO"=7777 AND "EMP"."ENAME"='RICH' AND "EM

P"."DEPTNO"=30)

Note: rule based optimization

15 rows selected