查詢訪問同一表的兩個以上索引（一）
經常看到有人提出這樣的疑問，我在同一張表上建立了多個索引，為什麼Oracle每次都選擇一個，而不能同時利用多個索引呢。一般來說，常見的訪問同一張表的兩個以上索引，存在三種情況，AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。
此外，還有一個設計上的疑問，如果有A、B、C三個欄位，都可能作為查詢條件，是建立多個複合索引好，還是建立三個單列的索引。這個問題之所以不好回答是因為和業務或者說和查詢的模式有很大的關係，不過如果理解了Oracle什麼時候會選擇一個以上的索引來訪問表，就會對於理解如何設計合理的索引有很大的幫助。
簡單介紹一下AND-EQUAL執行計劃。
首先建立一個測試表：
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> COMMIT;
Commit complete.
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.
現在建立了一個測試表和兩個索引，下面首先來看看AND-EQUAL執行方式：
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
ID NAME TYPE
---------- ------------------------------ ------------------------------
75688 T_DOUBLE_IND TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 474554719
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND |
| 2 | AND-EQUAL | |
|* 3 | INDEX RANGE SCAN | IND_DOUBLE_NAME |
|* 4 | INDEX RANGE SCAN | IND_DOUBLE_TYPE |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("NAME"='T_DOUBLE_IND')
4 - access("TYPE"='TABLE')
Note
-----
- rule based optimizer used (consider using cbo)
由於指定了兩個列，且兩個列上都包含索引，Oracle選擇了掃描兩個索引，並使用了AND-EQUAL執行計劃。這種掃描方式是分別透過兩個索引獲取索引鍵值對應的ROWID，然後合併兩個掃描中相等的ROWID，並透過這個ROWID來掃描表。
不過觀察執行計劃可以發現，當前的最佳化模式是RBO，如果收集一下索引，再次執行同樣的查詢：
SQL> SHOW PARAMETER OPTIMIZER_MODE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
ID NAME TYPE
---------- ------------------------------ ------------------------------
75688 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')
可以看到，收集統計資訊後，最佳化模式變為CBO，但是Oracle只選擇了一個索引進行掃描。對於當前的情況，NAME列的選擇度非常高，因此這種方式的代價最低。
SQL> SELECT /*+ INDEX(A IND_DOUBLE_NAME) INDEX(A IND_DOUBLE_TYPE) */
2 ID, NAME, TYPE
3 FROM T_DOUBLE_IND
4 WHERE NAME = 'T_DOUBLE_IND'
5 AND TYPE = 'TABLE';
ID NAME TYPE
---------- ------------------------------ ------------------------------
75689 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')
即使透過HINT指定兩個索引，CBO最佳化器也只是會選擇其中一個索引來進行掃描，而自動忽略另一個選擇度低的索引。
只有使用AND_EQUAL提示，才能在CBO的情況下使用AND-EQUAL執行計劃：
SQL> SELECT /*+ AND_EQUAL(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';
Execution Plan
----------------------------------------------------------
Plan hash value: 474554719
--------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 21 (0)| 00:00:01 |
|*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 21 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|*3 | INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | IND_DOUBLE_TYPE | 9286 | | 20 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
3 - access("NAME"='T_DOUBLE_IND')
4 - access("TYPE"='TABLE')
在CBO下，Oracle不會自動選擇AND-EQUAL執行計劃，這是因為Oracle可以根據直方圖來判斷一個列上各個值的選擇度，但是從統計資訊無法獲得兩個不同的列合AND-EQUAL之後的選擇度，這個操作後，如果得到的ROWID記錄很少，那麼這個查詢的效率就會很高，如果AND-EQUAL後得到大量的ROWID，那麼查詢的效率就會很差，這時應該選擇全表掃描或其他的執行計劃。由於CBO都是根據統計資訊分析得到的結果，而AND-EQUAL的結果對於CBO是未知的，因此CBO不會選擇AND-EQAUL這種掃描方式。
如果要Oracle獲得這種統計資訊，最簡單的方法就是建立一個複合索引，Oracle在分析索引列的時候自動會分析兩個列的組合情況，從而在查詢的時候可以準確的判斷是否應該使用這個複合索引。
SQL> SET AUTOT OFF
SQL> CREATE INDEX IND_DOU_NAM_TYP
2 ON T_DOUBLE_IND
3 (NAME, TYPE);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SET AUTOT TRACE EXP
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
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 | | 2 | 74 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 2 | 74 | 3 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
SQL> SET AUTOT OFF
SQL> UPDATE T_DOUBLE_IND
2 SET NAME = 'T_DOUBLE_IND'
3 WHERE ID <= 20000;
20000 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 TRACE EXP
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
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 | | 2277 | 77418 | 1225 (1)| 00:00:18 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 2277 | 77418 | 1225 (1)| 00:00:18 |
|*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 2277 | | 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
SQL> SET AUTOT OFF
SQL> UPDATE T_DOUBLE_IND
2 SET TYPE = 'TABLE'
3 WHERE ID <= 20000;
20000 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 TRACE EXP
SQL> SELECT ID, NAME, TYPE
2 FROM T_DOUBLE_IND
3 WHERE NAME = 'T_DOUBLE_IND'
4 AND TYPE = 'TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 4134729579
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7778 | 258K| 2010 (1)| 00:00:29 |
|* 1 | TABLE ACCESS FULL| T_DOUBLE_IND | 7778 | 258K| 2010 (1)| 00:00:29 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
在建立複合索引後，對於剛才的查詢，Oracle會自動選擇複合索引。即使將表中的20000條記錄全部更新為NAME=’T_DOUBLE_BIN’，由於TYPE列的選項條件，在加上索引相對於表來說要窄得多，Oracle仍然選擇了複合索引掃描。
如果將TYPE列也更新20000條記錄，使得NAME=’T_DOUBLE_IND’和TYPE=’TABLE’的選擇性很差，這時Oracle就會自動選擇全表掃描來代替索引掃描。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-661830/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢訪問同一表的兩個以上索引（三）索引
- 查詢訪問同一表的兩個以上索引（二）索引
- zt_如何用一個表的兩個以上索引訪問表_sql tuning_sql優化索引SQL優化
- 一個查詢不走索引的例子索引
- 一個使用JDBC按Date查詢查詢的問題JDBC
- 兩個TABALE在聯合查詢很慢的問題
- 查詢某個表的索引資訊索引
- 一個MySQL多表查詢的問題MySql
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 兩個資料訪問受限的問題
- 同一張表的兩個欄位比較查詢
- 【最佳化】模糊查詢索引問題索引
- MySQL 唯一索引範圍查詢鎖下一個記錄的理解MySql索引
- SQL 兩個表組合查詢SQL
- union的兩個子查詢是否並行並行
- 查詢兩個日期之間的資料
- 一個很簡單的查詢,為什麼用不到索引索引
- Oracle訪問索引的執行計劃（一）Oracle索引
- efcore 跨表查詢，實現一個介面內查詢兩個不同資料庫裡各自的表資料資料庫
- 一個SQL查詢連續三天的流量100以上的資料值【SQql Server】SQLServer
- Linq兩個from查詢條件
- cassandra的索引查詢和排序索引排序
- elasticsearch之多索引查詢Elasticsearch索引
- Elasticsearch（三）：索引查詢Elasticsearch索引
- 查詢索引 常用SQL索引SQL
- 查詢相似的索引索引
- 一個詭異的MySQL查詢超時問題，居然隱藏著存在了兩年的BUGMySql
- [MySQL] - 聯表查詢，查詢一個不在另一個表的記錄MySql
- 優化訪問相關 datetime 列的查詢優化
- mysql 5.7.11查詢分割槽表的一個問題MySql
- oracle 10.2.0.4執行一個樹查詢的問題Oracle
- 同一欄位多個查詢條件時遇到的一個問題
- SYS查詢不到的同義詞
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- 索引監控-查詢從未被使用過的索引索引
- Oracle 索引訪問方式Oracle索引
- MySql 查詢某一天日期格式欄位走索引問題MySql索引