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

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

| Id   | Operation             | Name               | Rows   | Bytes | Cost (%CPU)| Time      |

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

|    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

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

| Id   | Operation             | Name               | Rows   | Bytes | Cost (%CPU)| Time      |

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

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

如果,结果集中可能出现nulls

doudou@TEST> select object_id from doudou01 ;

40930 rows selected.

Execution Plan

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

Plan hash value: 2512695616

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

| Id   | Operation          | Name      | Rows   | Bytes | Cost (%CPU)| Time      |

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

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

3 、 总结

fast full index scan 条件： 查询的结果集列都是索引列且结果集中无nulls