index range scan & fast full scan

index fast full scan (ffs) 用在 查询语句里select 一个或几个字段而且where子句里必须有这些字段的一部分. ,这几个字段在同一个索引里,同时保证至少有一个字段是不为空的,not null.

1.All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.
2.The query returns more than 10 percent of the rows within the index. This 10 percent figure depends . the degree of multi-block reads and the degree of parallelism.
3.You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

下面是定义和说明在oracle 10g 文档中

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

You can specify fast full index scans 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.

如下是找的一些例子

analyze table aa_t compute statistics;

-- Create/Recreate indexes
create index INDE_AA_T on AA_T (OWNER)

create index INDE_AA_T1 on AA_T (OBJECT_ID)

create index INDE_AA_T_FULL on AA_T (OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE)

注意object_id这字段非空,

当执行 select object_id from aa_t ; 会是ffs

但是当执行 select object_id from aa_t where object_id =2 ;

时 不是ffs, 而是 index range scan

似乎是当把 非空的那个字段放在 where子句中,就不能成为 ffs, 好像因为从where子句中定位了某个非空的那个字段的具体值,所以不走ffs了.

例子一: index fast full scan

SQL> set timing on
SQL> set autotrace traceonly exp
SQL> select /* index_ffs(aa_t inde_aa_t_full)*/ object_type
2 from aa_t
3 where object_type='INDEX';

用时: 00: 00: 00.00

执行计划;®
----------------------------------------------------------
Plan hash value: 1344549076

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

| Id | Operation            | Name           | Rows | Bytes | Cost (%CPU)| Tim
e     |

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

|   0 | SELECT STATEMENT     |                | 1924 | 15392 |    43   (5)| 00:
00:01 |

|* 1 | INDEX FAST FULL SCAN| INDE_AA_T_FULL | 1924 | 15392 |    43   (5)| 00:
00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

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

例子2:index range scan:

SQL> select /* index_ffs(aa_t inde_aa_t_full)*/ object_TYPE,OBJECT_ID
2 from aa_t
3 where object_ID=2;
ÒÑÓÃʱ¼ä: 00: 00: 00.00

Ö´Ðмƻ®
----------------------------------------------------------
Plan hash value: 4121817606

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

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

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

|   0 | SELECT STATEMENT |                |     1 |    12 |     2   (0)| 00:00:0
1 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=2)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12712263/viewspace-606957/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12712263/viewspace-606957/