查詢訪問同一表的兩個以上索引（三）
經常看到有人提出這樣的疑問，我在同一張表上建立了多個索引，為什麼Oracle每次都選擇一個，而不能同時利用多個索引呢。一般來說，常見的訪問同一張表的兩個以上索引，存在三種情況，AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。
此外，還有一個設計上的疑問，如果有A、B、C三個欄位，都可能作為查詢條件，是建立多個複合索引好，還是建立三個單列的索引。這個問題之所以不好回答是因為和業務或者說和查詢的模式有很大的關係，不過如果理解了Oracle什麼時候會選擇一個以上的索引來訪問表，就會對於理解如何設計合理的索引有很大的幫助。
簡單介紹一下INDEX HASH JOIN執行計劃。
查詢訪問同一表的兩個以上索引（一）：http://yangtingkun.itpub.net/post/468/499769
查詢訪問同一表的兩個以上索引（二）：http://yangtingkun.itpub.net/post/468/499798
首先建立一個測試表：
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;
76058 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.
現在建立了一個測試表和兩個索引，下面看看如何利用索引雜湊連線：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SET AUTOT ON EXP
SQL> SELECT NAME, TYPE
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
NAME TYPE
------------------------------ ------------------------------
T_DOUBLE_IND TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 3887138334
--------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 32 | 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_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ NAME, TYPE
2 FROM T_DOUBLE_IND A
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
NAME TYPE
------------------------------ ------------------------------
T_DOUBLE_IND TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 1096369674
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 6 (17)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 32 | 6 (17)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| IND_DOUBLE_NAME | 1 | 32 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IND_DOUBLE_TYPE | 1 | 32 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
2 - access(ROWID=ROWID)
3 - access("NAME"='T_DOUBLE_IND')
4 - access("TYPE"='TABLE')
利用INDEX_JOIN提示，可以使得Oracle選擇INDEX HASH JOIN連線方式，從而避免對錶進行訪問。Oracle選擇兩個索引進行範圍掃描，然後執行HASH JOIN，而HASH JOIN的連線列是ROWID，最後透過一個內部檢視執行查詢條件的過濾。
不過這種執行計劃的前提是，查詢所選擇的列必須能夠透過索引完全的提供，如果包含了索引中不存在的列，Oracle是不會再次選擇掃描表的。
SQL> SELECT /*+ INDEX_JOIN(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
---------- ------------------------------ ------------------------------
70924 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')
不僅如此，就是選擇了索引中包含的ROWID資訊，Oracle仍然會不會選擇INDEX HASH JOIN：
SQL> SELECT /*+ INDEX_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ROWID, NAME, TYPE
2 FROM T_DOUBLE_IND A
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
ROWID NAME TYPE
------------------ ------------------------------ ------------------------------
AAAjc5AAVAAAqQUAAD T_DOUBLE_IND TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 3887138334
--------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 3 (0)| 00:00:01 |
|*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 44 | 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')
其實這是沒有道理的，因為索引中本身就包括ROWID資訊，而且兩個索引做HASH JOIN的時候，連線列就是ROWID，因此這個查詢本來應該可以透過INDEX HASH JOIN來實現的，可能CBO最佳化器在處理這個演算法的時候忽略了這個問題。
當然，查詢COUNT(*)的操作也是可以利用INDEX HASH JOIN的：
SQL> SELECT /*+ INDEX_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ COUNT(*)
2 FROM T_DOUBLE_IND A
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2001662014
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 6 (17)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 32 | | |
|* 2 | VIEW | index$_join$_001 | 1 | 32 | 6 (17)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
|* 4 | INDEX RANGE SCAN| IND_DOUBLE_NAME | 1 | 32 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN| IND_DOUBLE_TYPE | 1 | 32 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
3 - access(ROWID=ROWID)
4 - access("NAME"='T_DOUBLE_IND')
5 - access("TYPE"='TABLE')
Oracle會自動根據統計資訊權衡是否選擇INDEX HASH JOIN，而不需要透過HINT來強制執行：
SQL> SET AUTOT OFF
SQL> UPDATE T_DOUBLE_IND
2 SET NAME = 'T_DOUBLE_IND'
3 WHERE ID <= 2000;
2000 rows updated.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SET AUTOT TRACE EXP
SQL> SELECT NAME, TYPE
2 FROM T_DOUBLE_IND A
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1096369674
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 259 | 8288 | 19 (6)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 259 | 8288 | 19 (6)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| IND_DOUBLE_NAME | 259 | 8288 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IND_DOUBLE_TYPE | 259 | 8288 | 13 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
2 - access(ROWID=ROWID)
3 - access("NAME"='T_DOUBLE_IND')
4 - access("TYPE"='TABLE')
但是由於INDEX HASH JOIN的限制，使得這個查詢只能滿足查詢索引列或COUNT(*)的情況，因此使得這個執行計劃的使用機會大大降低。
而如果建立複合索引，講會使得查詢代價大為降低，而且可以滿足繼續掃描表的查詢要求：
SQL> CREATE INDEX IND_DOU_NAM_TYP
2 ON T_DOUBLE_IND (NAME, TYPE);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SELECT NAME, TYPE
2 FROM T_DOUBLE_IND A
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 3922347956
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240 | 7680 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_DOU_NAM_TYP | 240 | 7680 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND A
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1808990274
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240 | 8880 | 149 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 240 | 8880 | 149 (0)| 00:00:03 |
|*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 240 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
簡單總結一下，根據這三篇文章介紹的同時訪問兩個以上索引的三種情況而言，AND-EQUAL執行計劃在Oracle已經不在推薦，連AND-EQUAL這個HINT在文件中也不再介紹，只是為了後向相容性而繼續保留。原因已經介紹過，Oracle無法透過統計資訊來判斷這種執行路徑的代價。而INDEX HASH JOIN則由於限制條件的問題很難廣泛使用。唯一對於多個單列索引而言的優勢在於BITMAP AND/OR操作，而一般這種執行計劃在資料倉儲型別的查詢中更為常見。因此，對於要求快速得到查詢結果的OLTP系統中，複合索引應該是更為合理的選擇，不要希望透過建立多個單列索引，來完全代替複合索引的建立。
最後，什麼索引最適合你的系統仍然要具體情況具體分析，實踐才是檢驗索引的唯一真理。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-661917/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢訪問同一表的兩個以上索引（二）索引
- 查詢訪問同一表的兩個以上索引（一）索引
- zt_如何用一個表的兩個以上索引訪問表_sql tuning_sql優化索引SQL優化
- Elasticsearch（三）：索引查詢Elasticsearch索引
- 兩個TABALE在聯合查詢很慢的問題
- 查詢某個表的索引資訊索引
- 一個查詢不走索引的例子索引
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- Oracle訪問索引的執行計劃（三）Oracle索引
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 兩個資料訪問受限的問題
- 一個SQL查詢連續三天的流量100以上的資料值【SQql Server】SQLServer
- 【最佳化】模糊查詢索引問題索引
- Oracle 索引的三個問題(轉)Oracle索引
- SQL 兩個表組合查詢SQL
- union的兩個子查詢是否並行並行
- 查詢兩個日期之間的資料
- 一個使用JDBC按Date查詢查詢的問題JDBC
- Linq兩個from查詢條件
- cassandra的索引查詢和排序索引排序
- elasticsearch之多索引查詢Elasticsearch索引
- 查詢索引 常用SQL索引SQL
- 查詢相似的索引索引
- 優化訪問相關 datetime 列的查詢優化
- SYS查詢不到的同義詞
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- 一個MySQL多表查詢的問題MySql
- 索引監控-查詢從未被使用過的索引索引
- Oracle 索引訪問方式Oracle索引
- 使用PowerBuilder同時訪問多個資料庫UI資料庫
- 走索引掃描的慢查詢索引
- 提升網站訪問速度的 SQL 查詢優化技巧網站SQL優化
- 【索引】反向索引--條件 範圍查詢索引
- MongoDB慢查詢與索引MongoDB索引
- 字母索引查詢ListView元件索引View元件
- mysql查詢索引結構MySql索引
- 反向索引與模糊查詢索引
- 優化-mysql子查詢索引失效問題解決優化MySql索引