【oracle 效能優化】組合索引查詢。
在Oracle中可以建立組合索引，即同時包含兩個或兩個以上列的索引。組合索引的使用存在著一定的侷限，只有在謂詞中出現全部索引列時才能使用效率最高的index unique scan, 否則謂詞中必須包含前導列，否則會走Index full scan或者FTS。
SQL> create index idx_test on yangtest (object_type,object_name);
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'YANGTEST',cascade=>true);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 20.78
SQL> select object_type,count(*) from yangtest group by object_type order by 2;
OBJECT_TYPE COUNT(*)
------------------- ----------
EDITION 1
RULE 1
MATERIALIZED VIEW 1
SCHEDULE 2
WINDOW GROUP 4
DIRECTORY 5
UNDEFINED 6
LOB PARTITION 7
RESOURCE PLAN 7
CONTEXT 7
WINDOW 9
CLUSTER 10
JOB 11
EVALUATION CONTEXT 11
INDEXTYPE 11
JOB CLASS 13
CONSUMER GROUP 14
RULE SET 17
PROGRAM 18
QUEUE 33
OPERATOR 57
XML SCHEMA 91
TABLE PARTITION 108
INDEX PARTITION 128
PROCEDURE 131
LIBRARY 179
TYPE BODY 224
SEQUENCE 227
FUNCTION 296
JAVA DATA 324
TRIGGER 482
LOB 760
JAVA RESOURCE 833
PACKAGE BODY 1206
PACKAGE 1267
TABLE 2543
TYPE 2616
INDEX 3194
VIEW 4749
JAVA CLASS 22103
SYNONYM 26670
已選擇41行。
已用時間: 00: 00: 00.09
1、當使用基於規則的優化器（RBO）時，只有當組合索引的前導列出現在SQL語句的where子句中時，才會使用到該索引；
SQL> set autot trace
SQL> select /*+ rule */ * from yangtest where object_type='JOB';
已選擇11行。
已用時間: 00: 00: 00.07
執行計劃
----------------------------------------------------------
Plan hash value: 2067289980
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST |
|* 2 | INDEX RANGE SCAN | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='JOB')
Note
-----
- rule based optimizer used (consider using cbo)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
2310 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
-- 而讓CBO自己選擇時，卻選擇了TFS，從資訊統計裡面可以看出consistent gets 是前者的100倍。CBO 也不一定很聰明。
SQL> select * from yangtest where object_type='JOB';
已選擇11行。
已用時間: 00: 00: 00.03
執行計劃
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1668 | 164K| 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 1668 | 164K| 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='JOB')
統計資訊
----------------------------------------------------------
264 recursive calls
0 db block gets
1050 consistent gets
0 physical reads
0 redo size
2006 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
由於使用了組合索引的前導列並且訪問了表中的少量記錄，Oracle明智地選擇了索引掃描。那麼，如果我們訪問表中的大量資料時，Oracle會選擇什麼樣的訪問路徑呢？看下面的測試：
SQL> select * from yangtest where object_type='SYNONYM';
已選擇26670行。
已用時間: 00: 00: 01.42
執行計劃
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1668 | 164K| 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 1668 | 164K| 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='SYNONYM')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
2769 consistent gets
0 physical reads
0 redo size
1228701 bytes sent via SQL*Net to client
19963 bytes received via SQL*Net from client
1779 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26670 rows processed
測試一下是使用RULE 的優化器。
SQL> select /*+ rule */ * from yangtest where object_type='SYNONYM';
已選擇26670行。
已用時間: 00: 00: 01.56
執行計劃
----------------------------------------------------------
Plan hash value: 2067289980
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST |
|* 2 | INDEX RANGE SCAN | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='SYNONYM')
Note
-----
- rule based optimizer used (consider using cbo)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
23543 consistent gets --明顯比cbo的執行計劃的多10倍。
0 physical reads
0 redo size
3235078 bytes sent via SQL*Net to client
19963 bytes received via SQL*Net from client
1779 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26670 rows processed
從以上結果可以看出，在訪問大量資料的情況下，使用索引確實會導致更高的執行成本，這從statistics部分的邏輯讀取數（consistent gets）就可以看出，使用索引導致的邏輯讀取數是不使用索引導致的邏輯讀的10倍還多。因此，Oracle明智地選擇了全表掃描而不是索引掃描。
下面，讓我們來看看where子句中沒有索引前導列的情況：
SQL> select * from yangtest where object_name ='EMP';
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 4208055961
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 45 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 2 | 202 | 45 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST | 2 | | 43 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='EMP')
filter("OBJECT_NAME"='EMP')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
35 consistent gets
1 physical reads
0 redo size
1337 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from yangtest where object_name ='YANGTEST';
未選定行
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 4208055961
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 45 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 2 | 202 | 45 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST | 2 | | 43 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='YANGTEST')
filter("OBJECT_NAME"='YANGTEST')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
沒有使用前導列，Oracle正確地選擇了索引跳躍掃描。我們再來看看如果不使用索引跳躍掃描，該語句的成本：
SQL> select /*+ NO_INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name ='DEPT';
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 2 | 202 | 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DEPT')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
1011 consistent gets --是使用索引跳躍掃描的50倍左右
0 physical reads
0 redo size
1335 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from yangtest where object_name like 'T%';
已選擇136行。
已用時間: 00: 00: 00.04
執行計劃
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 925 | 93425 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 925 | 93425 | 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" LIKE 'T%')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
1020 consistent gets
0 physical reads
0 redo size
8900 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
136 rows processed
這次只選擇了136條資料，跟表YANGTEST中總的資料量29489條相比，顯然只是很小的一部分，但是Oracle還是選擇了全表掃描，有1020 個邏輯讀。這種情況下，如果我們強制使用索引.結果如下
SQL> select /*+ INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name like 'T%';
已選擇136行。
已用時間: 00: 00: 00.06
執行計劃
----------------------------------------------------------
Plan hash value: 972231820
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 925 | 93425 | 1084 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 925 | 93425 | 1084 (1)| 00:00:14 |
|* 2 | INDEX FULL SCAN | IDX_TEST | 925 | | 424 (1)| 00:00:06 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'T%')
filter("OBJECT_NAME" LIKE 'T%')
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
537 consistent gets
0 physical reads
0 redo size
14700 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
136 rows processed
通過新增提示（hint），我們強制Oracle使用了索引掃描（index full scan），執行了335個邏輯讀，比使用全表掃描的時候少了一些。
由此可見，Oracle優化器有時會做出錯誤的選擇，因為它再“聰明”，也不如我們SQL語句編寫人員更清楚表中資料的分佈，在這種情況下，通過使用提示（hint），我們可以幫助Oracle優化器作出更好的選擇。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-678549/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【oracle 效能優化】組合索引之index_ssOracle優化索引Index
- MySQL-效能優化-索引和查詢優化MySql優化索引
- 組合索引的前導列與查詢——ORACLE索引Oracle
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- 查詢中讓優化器使用複合索引優化索引
- oracle效能優化（二）-調整查詢Oracle優化
- MySQL索引與查詢優化MySql索引優化
- EntityFramework優化：查詢效能Framework優化
- Oracle in 查詢優化Oracle優化
- 【效能優化】Oracle 效能優化：降低列值聚簇因子 提高查詢效率優化Oracle
- 效能優化之分頁查詢優化
- 全文查詢的效能優化優化
- 效能優化查詢語句優化
- MySQL 索引及查詢優化總結MySql索引優化
- MySQL索引原理及慢查詢優化MySql索引優化
- MongoDB範圍查詢的索引優化MongoDB索引優化
- 使用組合索引處理包含空值的查詢索引
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- Oracle效能優化之虛擬索引Oracle優化索引
- SQLServer索引優化（3）：對於建組合索引的要求SQLServer索引優化
- MySQL: 使用explain 優化查詢效能MySqlAI優化
- mysql查詢效能優化總結MySql優化
- SQLServer效能優化之查詢提示SQLServer優化
- 組合查詢 (轉)
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- msyql千萬級別查詢優化之索引優化索引
- MS SQL SERVER索引優化相關查詢SQLServer索引優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- ORACLE 組合索引 使用分析Oracle索引
- MySQL系列-- 4. 查詢效能優化MySql優化
- [MYSQL -17]組合查詢MySql
- MySQL 效能優化之索引優化MySql優化索引
- SUM優化（複合索引）優化索引
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- 使用點陣圖連線索引優化OLAP查詢索引優化
- 查詢優化優化
- 效能優化：索引下推優化索引
- 前端效能優化方案索引前端優化索引