查詢訪問同一表的兩個以上索引（二）
經常看到有人提出這樣的疑問，我在同一張表上建立了多個索引，為什麼Oracle每次都選擇一個，而不能同時利用多個索引呢。一般來說，常見的訪問同一張表的兩個以上索引，存在三種情況，AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。
此外，還有一個設計上的疑問，如果有A、B、C三個欄位，都可能作為查詢條件，是建立多個複合索引好，還是建立三個單列的索引。這個問題之所以不好回答是因為和業務或者說和查詢的模式有很大的關係，不過如果理解了Oracle什麼時候會選擇一個以上的索引來訪問表，就會對於理解如何設計合理的索引有很大的幫助。
簡單介紹一下BITMAP索引的AND/OR執行計劃。
查詢訪問同一表的兩個以上索引（一）：http://yangtingkun.itpub.net/post/468/499769
首先建立一個測試表：
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操作：
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';
ID NAME TYPE
---------- ------------------------------ ------------------------------
75737 T_DOUBLE_IND TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 2381509496
--------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 37| 5 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID | T_DOUBLE_IND | 1| 37| 5 (0)| 00:00:01 |
| 2| BITMAP CONVERSION TO ROWIDS | | | | | |
| 3| BITMAP AND | | | | | |
| 4| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5| INDEX RANGE SCAN | IND_DOUBLE_NAME | | | 1 (0)| 00:00:01 |
| 6| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7| INDEX RANGE SCAN | IND_DOUBLE_TYPE | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("NAME"='T_DOUBLE_IND')
7 - access("TYPE"='TABLE')
BITMAP索引的AND操作只有在CBO模式下才會啟用，因此收集表的統計資訊。由於根據當前的列的統計情況分析，使用NAME列上的索引代價最小，因此Oracle選擇了IND_DOUBLE_NAME索引掃描。
可以透過INDEX_COMBINE提示來指定索引進行BITMAP AND/OR操作，這種情況下，Oracle將兩個BTREE索引首先轉化為BITMAP索引，然後執行BITMAP AND或BITMAP OR的操作，根據需要決定是否再將BITMAP索引轉化回BTREE索引，然後根據ROWID訪問表得到最終的結果。
並非這種情況下一定需要提示，Oracle會自動根據統計資訊來判斷，是否應該進行BITMAP索引的轉化：
SQL> SET AUTOT OFF
SQL> UPDATE T_DOUBLE_IND
2 SET NAME = 'T_DOUBLE_IND'
3 WHERE TYPE = 'SYNONYM';
22600 rows updated.
SQL> COMMIT;
Commit complete.
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: 2581345372
--------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |2597|85701| 1219 (1)| 00:00:18 |
| 1| TABLE ACCESS BY INDEX ROWID | T_DOUBLE_IND |2597|85701| 1219 (1)| 00:00:18 |
| 2| BITMAP CONVERSION TO ROWIDS | | | | | |
| 3| BITMAP AND | | | | | |
| 4| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5| INDEX RANGE SCAN | IND_DOUBLE_TYPE |8456| | 12 (0)| 00:00:01 |
| 6| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7| INDEX RANGE SCAN | IND_DOUBLE_NAME |8456| | 75 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TYPE"='TABLE')
7 - access("NAME"='T_DOUBLE_IND')
在這個例子中，由於兩個索引的選擇性都很差，而基表本身由於存在一個長度為1000的列，因此全表掃描也是比較低效的，所以Oracle選擇透過兩個索引進行BITMAP AND操作來獲取記錄。
對於當前的查詢而已，這個選擇是很高效的，但是可以明顯的看到，Oracle的執行計劃中，預計返回行數以及COST值，都是十分不準確的。
而對於OR查詢條件的情況，一般來說Oracle會根據統計資訊來判斷是否選擇使用BITMAP OR執行計劃：
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T'
4 OR TYPE = 'CONTEXT';
ID NAME TYPE
---------- ------------------------------ ------------------------------
9766 LT_CTX CONTEXT
51595 T TABLE
70782 T TABLE
48576 EM_GLOBAL_CONTEXT CONTEXT
48577 EM_USER_CONTEXT CONTEXT
75651 T TABLE
75700 T TABLE
7047 REGISTRY$CTX CONTEXT
40742 DR$APPCTX CONTEXT
56564 T TABLE
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1253459974
--------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 21| 714| 12 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID | T_DOUBLE_IND | 21| 714| 12 (0)| 00:00:01 |
| 2| BITMAP CONVERSION TO ROWIDS | | | | | |
| 3| BITMAP OR | | | | | |
| 4| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5| INDEX RANGE SCAN | IND_DOUBLE_NAME | | | 1 (0)| 00:00:01 |
| 6| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7| INDEX RANGE SCAN | IND_DOUBLE_TYPE | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("NAME"='T')
7 - access("TYPE"='CONTEXT')
如果指定返回的結果集比較大，則Oracle不會傾向利用索引：
SQL> SET AUTOT TRACE EXP
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T_DOUBLE_IND'
4 OR TYPE = 'INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 4134729579
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29745 | 987K| 2010 (1)| 00:00:29 |
|* 1 | TABLE ACCESS FULL| T_DOUBLE_IND | 29745 | 987K| 2010 (1)| 00:00:29 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' OR "TYPE"='INDEX')
不過BITMAP索引天生時候回答COUNT(*)的問題，如果只是查詢記錄數，則CBO傾向於利用索引，而和索引的選擇性沒有關係：
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*)
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T_DOUBLE_IND'
4 OR TYPE = 'INDEX';
COUNT(*)
----------
33058
Execution Plan
----------------------------------------------------------
Plan hash value: 4098198664
--------------------------------------------------------------------------------------------
|Id| Operation | Name | Rows|Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 29 | 87 (2)|00:00:02|
| 1| SORT AGGREGATE | | 1| 29 | | |
| 2| BITMAP CONVERSION COUNT | |29745| 842K| 87 (2)|00:00:02|
| 3| BITMAP OR | | | | | |
| 4| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5| INDEX RANGE SCAN | IND_DOUBLE_NAME | | | 71 (0)|00:00:01|
| 6| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7| INDEX RANGE SCAN | IND_DOUBLE_TYPE | | | 15 (0)|00:00:01|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("NAME"='T_DOUBLE_IND')
7 - access("TYPE"='INDEX')
如果查詢中經常對多列進行限制條件，且大部分情況只需要COUNT(*)查詢，那麼可能在各個列上建立單列索引會更適合。不過這種情況下，一般在資料倉儲系統或報表系統中更加常見，而在這種型別的資料庫中，一般直接就建立BITMAP索引了。
但是在OLTP系統中，BITMAP索引基本上是不可能的選擇，因為這種型別的索引會極大的影響併發性，顯然BTREE索引才是正確的選擇。不過到底是單列索引還是複合索引，仍然沒有一個確切的答案，還是要看具體的情況進行分析。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-661902/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢訪問同一表的兩個以上索引（三）索引
- 查詢訪問同一表的兩個以上索引（一）索引
- zt_如何用一個表的兩個以上索引訪問表_sql tuning_sql優化索引SQL優化
- 為何在查詢中索引未被使用(二)——問題解答索引
- 【索引】反向索引--條件 範圍查詢（二）索引
- 查詢同時附帶查主表的第二列
- 兩個TABALE在聯合查詢很慢的問題
- 查詢某個表的索引資訊索引
- 用兩個二分查詢實現的海倫信封程式
- 一個查詢不走索引的例子索引
- Oracle訪問索引的執行計劃（二）Oracle索引
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 兩個資料訪問受限的問題
- 【最佳化】模糊查詢索引問題索引
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- SQL 兩個表組合查詢SQL
- union的兩個子查詢是否並行並行
- 查詢兩個日期之間的資料
- 分頁查詢的排序問題（二）排序
- 一個使用JDBC按Date查詢查詢的問題JDBC
- 二叉查詢樹的個數
- Linq兩個from查詢條件
- cassandra的索引查詢和排序索引排序
- elasticsearch之多索引查詢Elasticsearch索引
- Elasticsearch（三）：索引查詢Elasticsearch索引
- 查詢索引 常用SQL索引SQL
- 查詢相似的索引索引
- 優化訪問相關 datetime 列的查詢優化
- SYS查詢不到的同義詞
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- 查詢——二分查詢
- 一個MySQL多表查詢的問題MySql
- 索引監控-查詢從未被使用過的索引索引
- Oracle 索引訪問方式Oracle索引
- ElasticSearch的查詢（二）Elasticsearch
- 使用PowerBuilder同時訪問多個資料庫UI資料庫
- 走索引掃描的慢查詢索引