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
SQL>alter 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)
没有问题
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行。
SQL> set arraysize 1000;
SQL> alter system flush buffer_cache; ----一定要刷新，不然观察不到 db file sequential read
SQL> alter system flush shared_pool;
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> select object_id from test where rownum<11;
OBJECT_ID
----------
66266
66267
66268
66269
66270
66271
66272
66273
66274
66275
10 rows selected.
SQL> alter session set events '10046 trace name context off';
检查该索引所属文件号、段头快
SQL> select owner,header_file,header_block from dba_segments where segment_name='IND_TEST_ID';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
OWNER 4 3562
段头块为 3562,后退一个即 索引的 root block 3563
SQL> set arraysize 1000;
SQL> alter system flush buffer_cache; ----一定要刷新，不然观察不到 db file sequential read
SQL> alter system flush shared_pool;
SQL> alter session set events '10046 trace name context forever, level 8';
以下内容取自 10046 event trace文件
=====================
PARSING IN CURSOR #2 len=42 dep=0 uid=88 oct=3 lid=88 tim=1478672879417440 hv=3715463873 ad='cf77db60' sqlid='9rkncnzfrayq1'
select object_id from test where rownum<11
END OF STMT
PARSE #2:c=12998,e=13339,p=15,cr=61,cu=0,mis=1,r=0,dep=0,og=1,plh=1931801113,tim=1478672879417411
EXEC #2:c=0,e=103,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1931801113,tim=1478672879417635
WAIT #2: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1478672879417740
WAIT #2: nam='db file sequential read' ela= 24 file#=4 block#=3562 blocks=1 obj#=81680 tim=1478672879417839 --第四个数据文件的 3562数据块 也就是从段头块开始 ，依次读取 3563数据块 。3563数据块一次读入5个数据块
WAIT #2: nam='db file sequential read' ela= 11 file#=4 block#=21761 blocks=1 obj#=81680 tim=1478672879417916
WAIT #2: nam='db file sequential read' ela= 7 file#=4 block#=3561 blocks=1 obj#=81680 tim=1478672879417940
WAIT #2: nam='db file scattered read' ela= 5 file#=4 block#=11008 blocks=2 obj#=81680 tim=1478672879417964
WAIT #2: nam='db file scattered read' ela= 9 file#=4 block#=3563 blocks=5 obj#=81680 tim=1478672879418008
FETCH #2:c=1000,e=270,p=10,cr=12,cu=0,mis=0,r=1,dep=0,og=1,plh=1931801113,tim=1478672879418046
WAIT #2: nam='SQL*Net message from client' ela= 118 driver id=1650815232 #bytes=1 p3=0 obj#=81680 tim=1478672879418186
WAIT #2: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=81680 tim=1478672879418213
FETCH #2:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=1931801113,tim=1478672879418225
STAT #2 id=1 cnt=10 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=13 pr=10 pw=0 time=0 us)'
STAT #2 id=2 cnt=10 pid=1 pos=1 obj=81680 op='INDEX FAST FULL SCAN IND_TEST_ID (cr=13 pr=10 pw=0 time=0 us cost=2 size=40 card=10)'
WAIT #2: nam='SQL*Net message from client' ela= 239 driver id=1650815232 #bytes=1 p3=0 obj#=81680 tim=1478672879418502
*** SESSION ID:(1.13) 2016-11-09 14:27:59.419
结论：FFS会读取 段头块，并且会多块读
最开始扫描的是3562，它是索引的段头，并且是单块读(注意：段头都是单块读)，然后才是从3563 开始扫描，一共扫描了5个block 3563就是索引的root block
SQL> set arraysize 1000;
SQL> alter system flush buffer_cache; ----一定要刷新，不然观察不到 db file sequential read
SQL> alter system flush shared_pool;
SQL> alter session set events '10046 trace name context forever, level 8';
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.
SQL> alter session set events '10046 trace name context off';
以下内容取自 10046 event trace文件
=====================
PARSING IN CURSOR #4 len=72 dep=0 uid=88 oct=3 lid=88 tim=1478673548236909 hv=2159188642 ad='cf9c1348' sqlid='344baf60b56p2'
select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<11
END OF STMT
PARSE #4:c=27996,e=28261,p=17,cr=61,cu=0,mis=1,r=0,dep=0,og=1,plh=2443641574,tim=1478673548236908
EXEC #4:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2443641574,tim=1478673548236966
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1478673548237005
WAIT #4: nam='db file sequential read' ela= 10 file#=4 block#=3563 blocks=1 obj#=81680 tim=1478673548237648 --直接跳过 3562数据块(也就是跳过段头块)
WAIT #4: nam='db file scattered read' ela= 31 file#=4 block#=3564 blocks=4 obj#=81680 tim=1478673548237730 读取3564数据块 一次读入4个数据块
FETCH #4:c=1000,e=735,p=5,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2443641574,tim=1478673548237758
WAIT #4: nam='SQL*Net message from client' ela= 124 driver id=1650815232 #bytes=1 p3=0 obj#=81680 tim=1478673548237914
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=81680 tim=1478673548237949
FETCH #4:c=0,e=22,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=2443641574,tim=1478673548237962
STAT #4 id=1 cnt=10 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=5 pw=0 time=0 us)'
STAT #4 id=2 cnt=10 pid=1 pos=1 obj=81680 op='INDEX FULL SCAN IND_TEST_ID (cr=3 pr=5 pw=0 time=0 us cost=2 size=40 card=10)'
WAIT #4: nam='SQL*Net message from client' ela= 193 driver id=1650815232 #bytes=1 p3=0 obj#=81680 tim=1478673548238201
*** SESSION ID:(1.13) 2016-11-09 14:39:08.239
结论：这个索引的段头块是3562，root block就是段头+1 ，这里 root block 就是3563 ，根据实验可知，index full scan 没有扫描 segment header ,而是直接扫描 root block3563、leaf block 3564
结论：两者的结果完全不一样，这是为什么呢？
这是因为当进行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个块
归纳：
索引类别
访问方式
是否排序
FFS
先扫描 segment header，读取索引的段头，然后开始读取 root block、brunch block、leaf block
多一步 sort (order by)
FS
不扫描 segment header, 跳过索引的段头，而是直接扫描 root block、brunch block、leaf block
自动的执行 sort (order by)
详情
为什么 index fast full scan 要扫描 segment header呢？因为 index fast full scan 需要扫描所有的索引块(leaf block)，并且扫描不是有序的，是多块读，而且它不会回表，也就是说它不会解析出rowid，正是由于它要扫描所有的leaf block，并且是离散读，所以它必须读取segment header，不然Oracle怎么知道它读取了所有的 leaf block
为什么 index full scan 不扫描segment header? 因为 index full scan 是连续读的，由于leaf block之间有双向指针，Oracle不需要扫描segment header就能判断 leaf block 扫描完了没，它只需要从左往右，或者从右往左一直扫描到尽头即可。
转载于:https://www.cnblogs.com/iyoume2008/p/6051100.html
相关资源：ffs-zera：争取潜艇战线-源码