使用索引的誤區之一：沒有使用複合索引的前置列導致查詢不使用索引——oracle__oracle

  使用索引的誤區之一：沒有使用複合索引的前置列導致查詢不使用索引

http://blog.csdn.net/lunar2000/archive/2004/11/24/193470.aspx

在oracle中，我們經常以為建立了索引，sql查詢的時候就會如我們所希望的那樣使用索引，事實上，oracle只會在一定條件下使用索引，這裡我們總結數第一點：oracle會在條件中包含了前置列時使用索引，即查詢條件中必須使用索引中的第一個列，請看下面的例子

SQL> select * from tab;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

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

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   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

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   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

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

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

| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT            |             |       |       |       |

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

|*  2 |   INDEX RANGE SCAN          | EMP_ID1     |       |       |       |

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

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

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

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

| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT            |             |       |       |       |

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

|*  2 |   INDEX RANGE SCAN          | EMP_ID1     |       |       |       |

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

Predicate Information (identified by operation id):

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

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

Note: rule based optimization

 

15 rows selected

 

 </