Oracle如何查詢訪問同一表的兩個以上索引（二）

經常看到有人提出這樣的疑問，我在同一張表上建立了多個索引，為什麼Oracle每次都選擇一個，而不能同時利用多個索引呢。一般來說，常見的訪問同一張表的兩個以上索引，存在三種情況，AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。

此外，還有一個設計上的疑問，如果有A、B、C三個欄位，都可能作為查詢條件，是建立多個複合索引好，還是建立三個單列的索引。這個問題之所以不好回答是因為和業務或者說和查詢的模式有很大的關係，不過如果理解了Oracle什麼時候會選擇一個以上的索引來訪問表，就會對於理解如何設計合理的索引有很大的協助。

簡單介紹一下BITMAP索引的AND/OR執行計畫。

首先建立一個測試表：

SQL> DROP TABLE T_DOUBLE_IND PURGE;

Table dropped.

SQL> CREATE TABLE T_DOUBLE_IND

2  (ID NUMBER,

3  NAME VARCHAR2(30),

4  TYPE VARCHAR2(30),

5  CONTENTS VARCHAR2(4000));

Table created.

SQL> INSERT INTO T_DOUBLE_IND

2  SELECT ROWNUM,

3  OBJECT_NAME,

4  OBJECT_TYPE,

5  LPAD('A', 1000, 'A')

6  FROM DBA_OBJECTS;

75856 rows created.

SQL> CREATE INDEX IND_DOUBLE_NAME

2  ON T_DOUBLE_IND (NAME);

Index created.

SQL> CREATE INDEX IND_DOUBLE_TYPE

2  ON T_DOUBLE_IND (TYPE);

Index created.

現在建立了一個測試表和兩個索引，下面看看如何使用BITMAP索引的AND操作：

更多精彩內容：http://www.bianceng.cn/database/Oracle/

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

PL/SQL procedure successfully completed.

SQL> SET AUTOT ON EXP

SQL> SELECT ID, NAME, TYPE

2  FROM T_DOUBLE_IND

3  WHERE NAME = 'T_DOUBLE_IND'

4  AND TYPE = 'TABLE';

ID NAME                           TYPE

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

75737 T_DOUBLE_IND                   TABLE

Execution Plan

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

Plan hash value: 3887138334

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

| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |

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

| 0 | SELECT STATEMENT            |                 |    1 |    37 |     3   (0)| 00:00:01 |

|*1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    1 |    37 |     3   (0)| 00:00:01 |

|*2 |   INDEX RANGE SCAN          | IND_DOUBLE_NAME |    2 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("TYPE"='TABLE')

2 - access("NAME"='T_DOUBLE_IND')

SQL> SELECT /*+ INDEX_COMBINE(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE

2  FROM T_DOUBLE_IND A

3  WHERE NAME = 'T_DOUBLE_IND'

4  AND TYPE = 'TABLE';