INDEX FULL SCAN和INDEX FAST FULL SCAN的比较

INDEX FULL SCAN：
HINT写法：INDEX(表名 索引名)
原理：ORACLE定位到索引的ROOT BLOCK，然后到BRANCH BLOCK（如果有的话），再定位到第一个LEAF BLOCK, 然后根据LEAF BLOCK的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
INDEX FAST FULL SCAN：
HINT写法：INDEX_FFS(表名 索引名)
原理：从段头开始，读取包含位图块，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK，读取的顺序完全有物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。查询某个表记录总数的时候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。
测试：
1)        比较两者的COST
INDEX FULL SCAN的：
SELECT /*+ index(t) */ count(*) FROM tb_acc t;
----------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 | 10817   (1)| 00:03:15 |
|   1 |  SORT AGGREGATE|           |     1 |           |          |
|   2 |   INDEX FULL SCAN| PK_TB_ACC |   14M| 10817   (1)| 00:03:15 |
----------------------------------------------------------------------
INDEX FAST FULL SCAN的：
SELECT /*+ index_ffs(t) */ count(*) FROM tb_acc t;
---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     1 |  5200   (2)| 00:01:34 |
|   1 |  SORT AGGREGATE     |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_TB_ACC |    14M|  5200   (2)| 00:01:34 |
---------------------------------------------------------------------------
结论：INDEX FAST FULL SCAN的COST明显比INDEX FULL SCAN要低。
2)        INDEX FULL SCAN的一个应用
由于存在有效INDEX的情况下（ORDER BY后面的字段列表是某INDEX的前导列），返回的数据其实就是基于ORDER BY的字段排序过的，其实以下两条SQL的返回结果就完全等效了：
SELECT * FROM (
SELECT N_OPEN_DATE, N_ORG_ID, N_DEPOSIT_TYPE_ID FROM tb_acc t ORDER BY N_OPEN_DATE, N_ORG_ID, N_DEPOSIT_TYPE_ID)
WHERE ROWNUM < 10000000;
返回的结果等效于：
SELECT /*+ index(t ) */  N_OPEN_DATE, N_ORG_ID, N_DEPOSIT_TYPE_ID FROM tb_acc t WHERE ROWNUM < 10000000;
而比较两者的执行路径：
SELECT * FROM (
SELECT N_OPEN_DATE, N_ORG_ID, N_DEPOSIT_TYPE_ID FROM tb_acc t ORDER BY N_OPEN_DATE, N_ORG_ID, N_DEPOSIT_TYPE_ID)
WHERE ROWNUM < 10000000;
--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |  9999K|   371M|  9829   (1)| 00:02:57 |
|*  1 |  COUNT STOPKEY   |                        |       |       |            |        |
|   2 |   VIEW           |                        |  9999K|   371M|  9829   (1)| 00:02:57 |
|   3 |    INDEX FULL SCAN| IND_TB_ACC_N_OPEN_DATE |   14M|   219M|  9829   (1)| 00:02:57 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<10000000)

SELECT /*+ index(t ) */  N_OPEN_DATE, N_ORG_ID, N_DEPOSIT_TYPE_ID FROM tb_acc t WHERE ROWNUM < 10000000;
-------------------------------------------------------------------------------------------
| Id  | Operation        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                        |  9999K|   152M|  9829   (1)| 00:02:57 |
|*  1 |  COUNT STOPKEY  |                       |       |       |            |         |
|   2 |   INDEX FULL SCAN| IND_TB_ACC_N_OPEN_DATE |  9999K|   152M|  9829   (1)| 00:02:57 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<10000000)
结论：虽然两者的执行时间上几乎没有差别，但INDEX FAST FULL SCAN的执行计划比INDEX FULL SCAN少了一步。
要特别注意：
a)        SELECT ZFDM, MAIL_ID FROM t_mailchrginfo t WHERE ROWNUM < 2000;
以上SQL当有适合的INDEX时：10g中不加HINT会选择走INDEX FAST FULL SCAN；但9i中如果不加HINT就会走FTS，那返回的结果就是完全没有经过排序的原始记录了，差异非常之大。
b)        走INDEX FAST FULL SCAN时数据是部分排序，但这种排序规则是不可预知的（与INDEX的物理存取块相关），在实际应用中需要将其当成未排序来理解，以免造成数据上的错误。

[ 本帖最后由 netfairy 于 2010-2-25 16:29 编辑 ]

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

转载于:http://blog.itpub.net/4555/viewspace-711104/