其实Fast Full Index Scans是针对full table scan而言的,Fast Full Index Scans的一些特点也和full table scan相似(如：多块读)：

Fast Full Index Scans的一些特点：

1.Fast full scan is available only with the CBO

2.at least one column in the index key has the NOT NULL constraint

3. A fast full scan accesses the data in the index itself, without accessing the table

4.It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.（粗略的试验了一下发现也是排好序了，按照index_key）

5.It reads the entire index using multiblock reads, unlike a full index scan

6.Fast full index scans cannot be performed against bitmap indexes(这里有点问题，在10g，11g下测似没有问题啊)

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

8.There must be a parallel clause on the index if you want to perform a fast full index scan in parallel. The parallel degree of the index is set independently. The index does not inherit the degree of parallelism of the table.

9.You must have analyzed the index; otherwise, the optimizer might decide not to use it

[@more@]

SQL> create index idx_tt on tt(id);

索引已创建。

SQL> select /*+ index_ffs(a idx_tt) */ id from tt a;

ID
----------
6
0
1
2
3
4
5

已选择7行。

执行计划
----------------------------------------------------------
Plan hash value: 264906180

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TT | 14 | 28 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed

SQL> desc tt
名称是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME VARCHAR2(10)
AGE NUMBER(38)

--在index_column上至少有一列存在not null constraint
SQL> alter table tt modify id not null;

表已更改。

SQL> select id from tt ;

ID
----------
0
1
2
3
4
5
6

已选择7行。

执行计划
----------------------------------------------------------
Plan hash value: 4239276870

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 28 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_TT | 14 | 28 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

统计信息
----------------------------------------------------------
194 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
7 rows processed

SQL> create bitmap index idx_tt on tt(id);

索引已创建。

SQL> select /*+ index_ffs(tt idx_tt) */ id from tt ;

ID
----------
0
1
2
3
4
5
6

已选择7行。

执行计划
----------------------------------------------------------
Plan hash value: 2673852445

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

-------

e |

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

-------

| 0 | SELECT STATEMENT | | 14 | 28 | 1 (0)| 00:

00:01 |

| 1 | BITMAP CONVERSION TO ROWIDS | | 14 | 28 | 1 (0)| 00:

00:01 |

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

-------

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed

SQL>
BITMAP INDEX FAST FULL SCAN和INDEX FAST FULL SCAN是不是一回事?
--================================================
SQL> drop index idx_tt;

索引已删除。

SQL> create index idx_tt on tt(id) parallel 2;

索引已创建。

SQL> select index_name,degree from user_indexes where index_name='IDX_TT';

INDEX_NAME DEGREE
------------------------------ ----------------------------------------
IDX_TT 2

SQL> select /*+ index_ffs(tt idx_tt) */ id from tt ;

ID
----------
0
1
2
3
4
5
6

已选择7行。

执行计划
----------------------------------------------------------
Plan hash value: 3032270452

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

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

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

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

| 0 | SELECT STATEMENT | | 14 | 28 | 2 (0)| 00:00:

01 | | | |

| 1 | PX COORDINATOR | | | | |
| | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | 14 | 28 | 2 (0)| 00:00:

01 | Q1,00 | P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 14 | 28 | 2 (0)| 00:00:

01 | Q1,00 | PCWC | |

| 4 | INDEX FAST FULL SCAN| IDX_TT | 14 | 28 | 2 (0)| 00:00:

01 | Q1,00 | PCWP | |

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

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

统计信息
----------------------------------------------------------
27 recursive calls
3 db block gets
15 consistent gets
0 physical reads
716 redo size
491 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7 rows processed

SQL>
--===================================================================
按照id确实排序了！并没有象doc上说的不排序，还有待于验证！
SQL> insert into tt select *from tt;

已创建7行。

SQL> commit;

提交完成。

SQL> select /*+ index_ffs(tt idx_tt) */ id from tt ;

ID
----------
0
0
1
1
2
2
3
3
4
4
5

ID
----------
5
6
6

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 3032270452

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

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

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

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

| 0 | SELECT STATEMENT | | 14 | 28 | 2 (0)| 00:00:

01 | | | |

| 1 | PX COORDINATOR | | | | |
| | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | 14 | 28 | 2 (0)| 00:00:

01 | Q1,00 | P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 14 | 28 | 2 (0)| 00:00:

01 | Q1,00 | PCWC | |

| 4 | INDEX FAST FULL SCAN| IDX_TT | 14 | 28 | 2 (0)| 00:00:

01 | Q1,00 | PCWP | |

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

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

统计信息
----------------------------------------------------------
6 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
--=========================================================================================
测试使用db_file_multiblock_read_count的效果并不明显，随着db_file_multiblock_read_count的减小应该看到物理读有明显的增多才是，可是并没有看到物理读有显著的变化。
SQL> alter table t modify object_id not null;

表已更改。

SQL> set time on
10:41:42 SQL> set time off
SQL> set timing on
SQL> set autotrace off
SQL> set autotrace on
SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 01.57

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1188 (2)| 00:00:15 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
206 recursive calls
0 db block gets
4376 consistent gets
4335 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 00.34

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1188 (2)| 00:00:15 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4346 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> show parameter mul

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 43
parallel_adaptive_multi_user boolean TRUE
SQL> alter session set db_file_multiblock_read_count=8;

会话已更改。

已用时间: 00: 00: 00.00
SQL> show parameter mul

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8
parallel_adaptive_multi_user boolean TRUE

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.21
SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 02.46

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1188 (2)| 00:00:15 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
309 recursive calls
0 db block gets
4391 consistent gets
4349 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 00.28

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1188 (2)| 00:00:15 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4346 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.37

SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.21
SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 02.46

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1188 (2)| 00:00:15 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
309 recursive calls
0 db block gets
4391 consistent gets
4349 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 00.28

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1188 (2)| 00:00:15 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4346 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter session set db_file_multiblock_read_count=4;

会话已更改。

已用时间: 00: 00: 00.00
SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.64
SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.75
SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 01.89

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1639 (1)| 00:00:20 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 1639 (1)| 00:00:20 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
313 recursive calls
0 db block gets
4391 consistent gets
4349 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter session set db_file_multiblock_read_count=2;

会话已更改。

已用时间: 00: 00: 00.01
SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.81
SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.07
SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 02.35

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2540 (1)| 00:00:31 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 2540 (1)| 00:00:31 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
313 recursive calls
0 db block gets
4391 consistent gets
4349 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter session set db_file_multiblock_read_count=1;

会话已更改。

已用时间: 00: 00: 00.01
SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.48
SQL> alter system flush buffer_cache;

系统已更改。

已用时间: 00: 00: 00.06
SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用时间: 00: 00: 03.06

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4341 (1)| 00:00:53 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 4341 (1)| 00:00:53 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
313 recursive calls
0 db block gets
4391 consistent gets
4349 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
--================================================================================
试验证明确实只有在cbo下可以使用fast full index scan
SQL> alter session set optimizer_mode=choose;

会话已更改。

已用时间: 00: 00: 00.03
SQL> select count(*) from t;

COUNT(*)
----------
2019072

已用时间: 00: 00: 01.62

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 963 (2)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2019K| 963 (2)| 00:00:12 |
-----------------------------------------------------------------------

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4346 consistent gets
4239 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> analyze table t delete statistics ;

表已分析。

已用时间: 00: 00: 00.32
SQL> analyze index idx_t delete statistics ;

索引已分析

已用时间: 00: 00: 00.03
SQL> select count(*) from t;

COUNT(*)
----------
2019072

已用时间: 00: 00: 07.68

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------

Note
-----
- rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
27227 consistent gets
27217 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--有无statistics不重要，并不像doc说的You must have analyzed the index; otherwise, the optimizer might decide not to use it，重要的是optimizer_mode必须是cbo支持的模式才行。

SQL> alter session set optimizer_mode=all_rows;

会话已更改。

已用时间: 00: 00: 00.00
SQL> select count(*) from t;

COUNT(*)
----------
2019072

已用时间: 00: 00: 01.43

执行计划
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 969 (1)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T | 2086K| 969 (1)| 00:00:12 |
-----------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
4432 consistent gets
497 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

ITPub博客

Fast Full Index Scans的特点！

最新文章