index ffs、index fs原理考究-1109
1.创建测试表
SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
2.创建测试索引
SQL> CREATE INDEX ind_test_id ON TEST(object_id);
3.插入测试数据
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.
4.分析表 附带索引等等
SQL> analyze table test compute statistics for table for all columns for all indexes;
Table analyzed.
5.打开执行计划
SQL> set autotrace trace;
6.FFS示例
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
6.1修改字段属性 not null
SQL>alter table test modify(object_id not null);
6.2再次验证 FFS
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)
没有问题
7. IFS 示例
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行。
8结果验证
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';
8.1FFS(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.
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 |
8.2FS(INDEX FULL SCAN)
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个块
|
9.原因考证
归纳：
|
索引类别 |
访问方式 |
是否排序 |
|
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 扫描完了没，它只需要从左往右，或者从右往左一直扫描到尽头即可。 |
