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

 

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

| 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 |

|*  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

 

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

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

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

|   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