Fast full index scan 淺析

Fast full index scan 淺析

Fast Full Index Scan

A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order.

Fast full index scans are an alternative to a full table scan when both of the following conditions are met:

· The index must contain all columns needed for the query.

· A row containing all nulls must not appear in the query result set. For this result to be guaranteed, at least one column in the index must have either:

o A NOT NULL constraint

o A predicate applied to it that prevents nulls from being considered in the query result set

1、實驗表

1-1：實驗表doudou01

doudou@TEST> desc doudou01

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

索引

doudou@TEST> select index_name,column_name,table_name from user_ind_columns where table_name='DOUDOU01';

INDEX_NAME COLUMN_NAME TABLE_NAME

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

DOUDOU01_INDEX_ID OBJECT_ID DOUDOU01

1-2：實驗表doudou02

doudou@TEST> desc doudou02

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NOT NULL NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

索引

doudou@TEST> select index_name,column_name,table_name from user_ind_columns where table_name='DOUDOU02';

INDEX_NAME COLUMN_NAME TABLE_NAME

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

DOUDOU02_INDEX_ID OBJECT_ID DOUDOU02

2、Fast full index scan

1、索引必須包含查詢的所有列（均滿足）

2-1、索引列object_id not null 本身就約束了，查詢的資料不為null

2-2、索引列object_id 沒有限制not null ，但是where限制了查詢返回值不能為null

2-1（條件下）

doudou@TEST> select object_id from doudou02;

40930 rows selected.

Execution Plan

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

Plan hash value: 1737916282

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

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

| 0 | SELECT STATEMENT | | 37164 | 471K| 24 (0)| 00:00:01 |

| 1 | INDEX FAST FULL SCAN| DOUDOU02_INDEX_ID | 37164 | 471K| 24 (0)| 00:00:01 |

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

2-2（條件下）

doudou@TEST> select object_id from doudou01 where object_id>0;

40930 rows selected.

Execution Plan

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

Plan hash value: 2364134866

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

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

| 0 | SELECT STATEMENT | | 41421 | 525K| 24 (0)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| DOUDOU01_INDEX_ID | 41421 | 525K| 24 (0)| 00:00:01 |

如果,結果集中可能出現nulls

doudou@TEST> select object_id from doudou01 ;

40930 rows selected.

Execution Plan

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

Plan hash value: 2512695616

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

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

| 0 | SELECT STATEMENT | | 41421 | 525K| 131 (0)| 00:00:02 |

| 1 | TABLE ACCESS FULL| DOUDOU01 | 41421 | 525K| 131 (0)| 00:00:02 |

3、總結

fast full index scan條件：查詢的結果集列都是索引列且結果集中無nulls

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-750333/，如需轉載，請註明出處，否則將追究法律責任。

Fast full index scan 淺析

相關文章