各種索引型別發生的條件

index range scan(索引範圍掃描)：

1.對於unique index來說，如果where 條件後面出現了 ,between ...and...的時候，那麼就可能執行index range scan,如果where條件後面是=，那麼就會執行index unique scan。

2.對於none unique index來說 如果where 條件後面出現了=,>,

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 後面有 >,

SQL> select owner from test where wner='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 wner='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/16978544/viewspace-705691/，如需轉載，請註明出處，否則將追究法律責任。