indexfullscan与indexfastfullscan
Index Full Scan vs Index Fast Full Scan
index full scan和index fast full scan是指同样的东西吗？答案是no。两者虽然从字面上看起来差不多，但是实现的机制完全不同。我们一起来看看两者的区别在哪里?
首先来看一下IFS,FFS能用在哪里：在一句sql中，如果我们想搜索的列都包含在索引里面的话，那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句：
SQL CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
SQL CREATE INDEX ind_test_id ON TEST(object_id);
SQL INSERT INTO TEST
SELECT
*
FROM dba_objects
WHERE object_id IS NOT NULL AND object_id 10000
ORDER BY object_id DESC;
17837 rows created.
SQL analyze table test compute statistics for table for all columns for all indexes;
Table analyzed.
SQL set autotrace trace;
SQL select object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
1
0
TABLE ACCESS (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
这时候 Oracle会选择全表扫描，因为 object_id 列默认是可以为null的，来修改成 not null:
SQLalter table test modify(object_id not null);
SQL select object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
1
0
INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)
当然我们也可以使用index full scan:
SQL select/*+ index(test ind_TEST_ID)*/ object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
1
0
INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)
我们看到了两者都可以在这种情况下使用，那么他们有什么区别呢？有个地方可以看出两者的区别， 来看一下两者的输出结果，为了让大家看清楚一点，我们只取10行。
INDEX FAST FULL SCAN
SQL select object_id from test where rownum<11;
OBJECT_ID
----------
66266
66267
66268
66269
66270
66271
66272
66273
66274
66275
10 rows selected.
INDEX FULL SCAN
SQL select/*+ index(test ind_TEST_ID)*/ object_id from test
where rownum<11;
OBJECT_ID
----------
10616
12177
12178
12179
12301
13495
13536
13539
13923
16503
10 rows selected.
可以看到两者的结果完全不一样，这是为什么呢？这是因为当进行index full scan的时候 oracle定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
而index fast full scan则不同，它是从段头开始，读取包含位图块，root block,所有的branch block, leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。
这就是为什么两者的结果区别如此之大的原因，我们再仔细跟踪一下这两条语句。首先来看一下索引的结构
SQL select object_id from dba_objects where object_name='IND_TEST_ID';
OBJECT_ID
----------
70591
索引的object_id为70591，使用tree dump可以看到索引树的结构 SQL ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 70591';
----- begin tree dump
branch: 0x6809b8d 109091725 (0: nrow: 100, level: 1)
leaf: 0x6809b96 109091734 (-1: nrow: 294 rrow: 0)
leaf: 0x6c07ec1 113278657 (0: nrow: 262 rrow: 0)
leaf: 0x6c07ebd 113278653 (1: nrow: 518 rrow: 0)
leaf: 0x6c07eb1 113278641 (2: nrow: 524 rrow: 0)
leaf: 0x6c07ead 113278637 (3: nrow: 524 rrow: 0)
leaf: 0x6c07ea9 113278633 (4: nrow: 524 rrow: 0)
leaf: 0x6c07ea5 113278629 (5: nrow: 524 rrow: 0)
leaf: 0x6c07ea1 113278625 (6: nrow: 524 rrow: 0)
leaf: 0x6c07e9d 113278621 (7: nrow: 524 rrow: 0)
leaf: 0x6c07e99 113278617 (8: nrow: 524 rrow: 0)
leaf: 0x6c07e95 113278613 (9: nrow: 532 rrow: 0)
leaf: 0x6c07e91 113278609 (10: nrow: 524 rrow: 0)
leaf: 0x6c07e8d 113278605 (11: nrow: 524 rrow: 0)
leaf: 0x6c07ec8 113278664 (12: nrow: 524 rrow: 0)
leaf: 0x6c07ec4 113278660 (13: nrow: 524 rrow: 0)
leaf: 0x6c07ec0 113278656 (14: nrow: 524 rrow: 0)
leaf: 0x6c07ebc 113278652 (15: nrow: 524 rrow: 0)
leaf: 0x6809bb2 109091762 (16: nrow: 524 rrow: 0)
leaf: 0x6c07eb8 113278648 (17: nrow: 524 rrow: 0)
leaf: 0x6c07eb4 113278644 (18: nrow: 524 rrow: 0)
leaf: 0x6c07eb0 113278640 (19: nrow: 524 rrow: 0)
leaf: 0x6c07eac 113278636 (20: nrow: 524 rrow: 0)
leaf: 0x6809bae 109091758 (21: nrow: 524 rrow: 0)
leaf: 0x6c07ea8 113278632 (22: nrow: 524 rrow: 0)
leaf: 0x6c07ea4 113278628 (23: nrow: 524 rrow: 0)
leaf: 0x6c07ea0 113278624 (24: nrow: 105 rrow: 105)
leaf: 0x6c07e9c 113278620 (25: nrow: 129 rrow: 129)
leaf: 0x6c07eb9 113278649 (26: nrow: 123 rrow: 123)
leaf: 0x6809baa 109091754 (27: nrow: 246 rrow: 246)
leaf: 0x6c07e98 113278616 (28: nrow: 246 rrow: 246)
leaf: 0x6c07e94 113278612 (29: nrow: 246 rrow: 246)
leaf: 0x6809ba6 109091750 (30: nrow: 246 rrow: 246)
leaf: 0x6809bce 109091790 (31: nrow: 246 rrow: 246)
leaf: 0x6809bca 109091786 (32: nrow: 246 rrow: 246)
leaf: 0x6809c05 109091845 (33: nrow: 248 rrow: 248)
leaf: 0x6809c01 109091841 (34: nrow: 246 rrow: 246)
leaf: 0x6809bfd 109091837 (35: nrow: 246 rrow: 246)
leaf: 0x6809bf9 109091833 (36: nrow: 246 rrow: 246)
leaf: 0x6809bf5 109091829 (37: nrow: 246 rrow: 246)
leaf: 0x6809bf1 109091825 (38: nrow: 246 rrow: 246)
leaf: 0x6809bed 109091821 (39: nrow: 246 rrow: 246)
leaf: 0x6809be9 109091817 (40: nrow: 246 rrow: 246)
leaf: 0x6809be5 109091813 (41: nrow: 246 rrow: 246)
leaf: 0x6809be1 109091809 (42: nrow: 246 rrow: 246)
leaf: 0x6809bdd 109091805 (43: nrow: 246 rrow: 246)
leaf: 0x6809bd9 109091801 (44: nrow: 246 rrow: 246)
leaf: 0x6809bd5 109091797 (45: nrow: 246 rrow: 246)
leaf: 0x6809bd1 109091793 (46: nrow: 248 rrow: 248)
leaf: 0x6809bcd 109091789 (47: nrow: 246 rrow: 246)
leaf: 0x6809bc9 109091785 (48: nrow: 246 rrow: 246)
leaf: 0x6809c08 109091848 (49: nrow: 246 rrow: 246)
leaf: 0x6809c04 109091844 (50: nrow: 246 rrow: 246)
leaf: 0x6809c00 109091840 (51: nrow: 246 rrow: 246)
leaf: 0x6809bfc 109091836 (52: nrow: 246 rrow: 246)
leaf: 0x6809bf8 109091832 (53: nrow: 246 rrow: 246)
leaf: 0x6809bf4 109091828 (54: nrow: 246 rrow: 246)
leaf: 0x6809bf0 109091824 (55: nrow: 246 rrow: 246)
leaf: 0x6809bec 109091820 (56: nrow: 246 rrow: 246)
leaf: 0x6809be8 109091816 (57: nrow: 246 rrow: 246)
leaf: 0x6809be4 109091812 (58: nrow: 246 rrow: 246)
leaf: 0x6809be0 109091808 (59: nrow: 248 rrow: 248)
leaf: 0x6809bdc 109091804 (60: nrow: 246 rrow: 246)
leaf: 0x6809bd8 109091800 (61: nrow: 246 rrow: 246)
leaf: 0x6809b
