index range scan,index fast full scan,index skip scan發生的條件
index range scan(索引範圍掃描):
1.對於unique index來說,如果where 條件後面出現了<,> ,between ...and...的時候,那麼就可能執行index range scan,如果where條件後面是=,那麼就會執行index unique scan。
2.對於none unique index來說 如果where 條件後面出現了=,>,<,betweed...and...的時候,就有可能執行index range scan。
3.對於組合索引來說,如果where條件後面出現了組合索引的引導列,那麼可能執行index range scan。
index fast full scan(索引快速全掃描):
如果select 語句後面中的列都被包含在組合索引中,而且where後面沒有出現組合索引的引導列,並且需要檢索出大部分資料,那麼這個時候可能執行index fast full scan。index fast full scan 發生的條件:
1.必須是組合索引。2.引導列不在where條件中
index skip scan(索引跳躍式掃描)
當查詢可以透過組合索引得到結果,而且返回結果很少,並且where條件中沒有包含索引引導列的時候,可能執行index skip scan
索引跳躍式掃描發生的條件:
1.必須是組合索引。
2.引導列沒有出現在where條件中
下面透過一個簡單的實驗驗證一下上訴理論:
SQL> create table test as select * from dba_objects;
表已建立。
SQL> create unique index ind_id on test(object_id); ind_id是唯一索引
索引已建立。
SQL> create index ind_owner on test(owner); ind_owner是非唯一索引
索引已建立。
SQL> create index ooo on test(owner,object_name,object_type); ooo是組合索引
索引已建立。
SQL> exec dbms_stats.gather_table_stats('ROBINSON','TEST');
PL/SQL 過程已成功完成。
SQL> set autot trace
SQL> select owner from test where object_id=10;
執行計劃
----------------------------------------------------------
Plan hash value: 2544773305
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
SQL> select owner from test where object_id<10;
已選擇8行。
執行計劃
----------------------------------------------------------
Plan hash value: 1361604213
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 77 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 77 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 7 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
對於唯一索引,發生index range scan的時候就是返回多行記錄,where 後面有 >,<,between ..and..
SQL> select owner from test where owner='SCOTT';
已選擇13行。
執行計劃
----------------------------------------------------------
Plan hash value: 2280863269
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2936 | 17616 | 7 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_OWNER | 2936 | 17616 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
對於非唯一索引,即使where後面的限制條件是=,但是有可能返回多行,所以進行index range scan
SQL> select object_name,object_type from test where owner='ROBINSON';
已選擇15行。
執行計劃
----------------------------------------------------------
Plan hash value: 2845720098
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2936 | 114K| 23 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| OOO | 2936 | 114K| 23 (0)| 00:00:01 |
-------------------------------------------------------------------------
因為000不是唯一索引,而且where後面用到了索引ooo的引導列,所以進行index range scan.
SQL> select owner, object_name,object_type from test where object_name='EMP' ;
執行計劃
----------------------------------------------------------
Plan hash value: 1799988433
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 19 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | OOO | 2 | 80 | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------
因為查詢所需要的資訊可以透過索引ooo獲得,並且where後面沒有引導列owner,而且返回的行數很少(這裡只有一行),所以CBO選擇index skip scan,這裡autotrace沒有顯示返回多少行,下面顯示一下
SQL> set autot off
SQL> select owner, object_name,object_type from test where object_name='EMP' ;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------- -------------------
SCOTT EMP TABLE
SQL> select owner, object_name,object_type from test where object_type='INDEX';
已選擇1701行。
執行計劃
----------------------------------------------------------
Plan hash value: 3464522019
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1721 | 68840 | 70 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| OOO | 1721 | 68840 | 70 (3)| 00:00:01 |
-----------------------------------------------------------------------------
因為查詢所需的資訊可以透過索引ooo獲得,並且where後面沒有引導列owner,而且返回的行數較多(1701行),所以CBO選擇index fast full scan,這樣避免了全表掃描。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29893219/viewspace-1852737/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Index Full Scan vs Index Fast Full ScanIndexAST
- Index Full Scan 與 Index Fast Full ScanIndexAST
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- Index的掃描方式:index full scan/index fast full scanIndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN區別IndexAST
- index full scan 和 index FAST full scan 區別IndexAST
- Index Full Scan 與 Index Fast Full Scan (Final)IndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN的區別IndexAST
- index full scan 和 index fast full scan (IFS,FFS)的不同IndexAST
- [總結]關於index range scans & INDEX (FAST FULL SCAN)IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(上)IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(下)IndexAST
- index fast full scan 和 nullIndexASTNull
- Fast full index scan 淺析ASTIndex
- INDEX SKIP SCANIndex
- 高效的SQL(index skip scan使用條件)SQLIndex
- 理解index skip scanIndex
- [轉貼]Skip Scan IndexIndex
- 關於INDEX SKIP SCANIndex
- Index Range Scan (214)Index
- 索引優化index skip scan索引優化Index
- 收集full table / index scan sqlIndexSQL
- SELECT COUNT(*) 索引會走 index fast full scan索引IndexAST
- INDEX SKIP SCAN適用場景Index
- oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區別OracleIndexMySql
- index fast full scan不能使用並行的實驗IndexAST並行
- Clustered Index Scan and Clustered Index SeekIndex
- oracle hint_skip scan_index_ssOracleIndex
- FBI? MAX? INDEX FULL SCAN (MIN/MAX)?Index
- Index Range Scan成本與10053Index
- index skip scan的一些實驗。Index
- oracle實驗記錄(INDEX fast full scan 的成本計算)OracleIndexAST
- Index Unique Scan (213)Index
- 高效的SQL(index range scan優化排序)SQLIndex優化排序
- 跳躍式索引(Skip Scan Index)的淺析索引Index
- Index Range Scan成本 Histogram 和 10053IndexHistogram
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex