常見的index 相關的掃描方式大概有如下幾種：

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 rang scan 是根據索引的葉子block中資料去訪問表,和 key 大小順序一致

index skip scan(索引跳躍式掃描)

當查詢可以透過組合索引得到結果,而且返回結果很少,並且where條件中沒有包含索引引導列的時候,可能執行index skip scan

索引跳躍式掃描發生的條件:

1.必須是組合索引

2.引導列沒有出現在where條件中

index fast full scan(索引快速全掃描)：

index ffs 是說如果可以從INDEX上獲取select後面所有的列資料,且where 條件沒有組合索引的前導列,可能執行index ffs,不需要去訪問表；如果不可以從index上獲取某次訪問的資料,它可能會執行INDEX FULL SCAN,然後再透過ROWID去訪問DATA BLOCK!

發生的條件

1.必須是組合索引

2.組合索引的引導列不在where條件中

3 要返回的資料較多(無定性)

index fast full scan 是根據索引segment的extent去搜尋的,FFS跟FTS的原理類似,只是掃描index segment 而不是FTS 的table segment,一次讀可以是連續的多個index block,因此這樣出來的資料順序和索引順序並不一致。

而我們通常說的利用不上索引指的是 index range scan or other index scan,不是 index fast full scan 。

index fast full scan的前提是就像資料肯定存在索引中有(比如not null 的欄位,或者複合索引,bitmap索引等),然後索引segment比表segment小,透過索引segment能得到所需要資料,而不用去讀任何表的block,這樣IO將減少。

環境：使用tom的bigtable 指令碼建立一個表並建立如下索引：

YANG@yangdb> @bigtab

yang@YANGDB> create index ind_owner on bigtab(owner);

Index created.

yang@YANGDB> create index ind_type on bigtab(object_type);

Index created.

yang@YANGDB> create index ind_ooo on bigtab(owner,object_name,object_type);

Index created.

收集統計資訊：

yang@YANGDB> exec dbms_stats.gather_table_stats('YANG','BIGTAB',cascade=>true, method_opt=>'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

其中欄位id 為主鍵，並建立索引 idx_id！

YANG@yangdb> set autot on

YANG@yangdb> set autot trace

當where 條件是非等於號時，cbo會選擇index range scan

YANG@yangdb> select id from bigtab where id < 10;

9 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2204167725

------------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| BIGTAB_PK | 1 | 5 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"<10)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2 consistent gets

index range scan 是根據葉子節點的順序去尋找資料，資料出來和索引順序是一致的排好順序的，一次讀一個索引block和一個資料block，從上面的邏輯讀中可以看出來2個邏輯讀！

當where條件中對唯一索引使用等號過濾是，cbo選擇INDEX UNIQUE SCAN

YANG@yangdb> select id from bigtab where id = 10;

Execution Plan

----------------------------------------------------------

Plan hash value: 794512637

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

|* 1 | INDEX UNIQUE SCAN| BIGTAB_PK | 1 | 5 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"=10)

由於owner 是非唯一性索引，所以cbo選擇了IND_OWNER 並INDEX RANGE SCAN的執行計劃

YANG@yangdb> select owner,object_name ,object_type from bigtab where wner='YANG';

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 272829004

-----------------------------------------------------------------------------------------

------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| BIGTAB | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_OWNER | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='YANG')

之前在owner,object_name ,object_type上面建立了組合索引IND_OOO，下面where後面沒有使用前導列並且資料量佔總資料的40%，因此執行計劃選擇了INDEX FAST FULL SCAN！

YANG@yangdb> select owner,object_name ,object_type from bigtab where OBJECT_TYPE='TABLE';

22700 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2535972880

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 22862 | 625K| 71 (0)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| IND_OOO | 22862 | 625K| 71 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_TYPE"='TABLE')

object_name為 t1的記錄只有一條，且 where條件沒有使用前導列，故執行計劃使用 INDEX SKIP SCAN！

YANG@yangdb> select owner,object_name ,object_type from bigtab where OBJECT_NAME='T1';

1 rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2709512398

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50 | 1400 | 6 (0)| 00:00:01 |

|* 1 | INDEX SKIP SCAN | IND_OOO | 50 | 1400 | 6 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_NAME"='T1')

filter("OBJECT_NAME"='T1')

YANG@yangdb> select owner,object_name ,object_type from bigtab where OBJECT_NAME='T1' AND OBJECT_TYPE='YANG';

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2367137367

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| BIGTAB | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TYPE | 12 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME"='T1')

2 - access("OBJECT_TYPE"='YANG')

我們來看：

11.2.3.3

This scan returns, at most, a single rowid. Oracle Database performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

Index Range Scans

An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

If you require the data to be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.

In , the order has been imported from a legacy system, and you are querying the order by the reference used in the legacy system. Assume this reference is the order_date.

Index Skip Scans

Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The database determines the number of logical subindexes by the number of distinct values in the initial column. Skip scanning is advantageous when there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.

Fast Full Index Scans :

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

Full Table Scans :

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause.

When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

【oracle】index的幾種掃描方式

相關文章