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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-750333/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-750333/