[Oracle] Index Full Scan vs Index Fast Full Scan作者：汪海 (Wanghai) 日期：14-Aug-2005 出处：http://spaces.msn.com/members/wzwanghai/--------------------------------------------------------------------------------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。比如以下语句： -- 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结果验证--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 6627510 rows selected. --8.2FS(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 1650310 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个块。--9.原因考证 这就是为什么两者的结果区别如此之大的原因，我们再仔细跟踪一下这两条语句。首先来看一下索引的结构 --9.1 FFS 操作结果SQL> select object_id from dba_objects where object_name='IND_TEST_ID'; OBJECT_ID---------- 70591索引的object_id为70591，使用tree dump可以看到索引树的结构 --9.2 索引结构探究SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 70591';----- begin tree dumpbranch: 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: 0x6809bd4 109091796 (62: nrow: 246 rrow: 246) leaf: 0x6809bd0 109091792 (63: nrow: 246 rrow: 246) leaf: 0x6809bcc 109091788 (64: nrow: 246 rrow: 246) leaf: 0x6809c07 109091847 (65: nrow: 246 rrow: 246) leaf: 0x6809c03 109091843 (66: nrow: 246 rrow: 246) leaf: 0x6809bff 109091839 (67: nrow: 246 rrow: 246) leaf: 0x6809bfb 109091835 (68: nrow: 246 rrow: 246) leaf: 0x6809bf7 109091831 (69: nrow: 246 rrow: 246) leaf: 0x6809bf3 109091827 (70: nrow: 246 rrow: 246) leaf: 0x6809bef 109091823 (71: nrow: 246 rrow: 246) leaf: 0x6809beb 109091819 (72: nrow: 248 rrow: 248) leaf: 0x6809be7 109091815 (73: nrow: 246 rrow: 246) leaf: 0x6809be3 109091811 (74: nrow: 246 rrow: 246) leaf: 0x6809bdf 109091807 (75: nrow: 246 rrow: 246) leaf: 0x6809bdb 109091803 (76: nrow: 246 rrow: 246) leaf: 0x6809bd7 109091799 (77: nrow: 246 rrow: 246) leaf: 0x6809bd3 109091795 (78: nrow: 246 rrow: 246) leaf: 0x6809bcf 109091791 (79: nrow: 246 rrow: 246) leaf: 0x6809bcb 109091787 (80: nrow: 246 rrow: 246) leaf: 0x6809c06 109091846 (81: nrow: 246 rrow: 246) leaf: 0x6809c02 109091842 (82: nrow: 246 rrow: 246) leaf: 0x6809bfe 109091838 (83: nrow: 246 rrow: 246) leaf: 0x6809bfa 109091834 (84: nrow: 246 rrow: 246) leaf: 0x6809ba2 109091746 (85: nrow: 129 rrow: 129) leaf: 0x6c07eb5 113278645 (86: nrow: 123 rrow: 123) leaf: 0x6809bf6 109091830 (87: nrow: 246 rrow: 246) leaf: 0x6809bf2 109091826 (88: nrow: 246 rrow: 246) leaf: 0x6809bee 109091822 (89: nrow: 246 rrow: 246) leaf: 0x6809bea 109091818 (90: nrow: 246 rrow: 246) leaf: 0x6809b9e 109091742 (91: nrow: 246 rrow: 246) leaf: 0x6809be6 109091814 (92: nrow: 246 rrow: 246) leaf: 0x6809be2 109091810 (93: nrow: 246 rrow: 246) leaf: 0x6809bde 109091806 (94: nrow: 246 rrow: 246) leaf: 0x6809bda 109091802 (95: nrow: 246 rrow: 246) leaf: 0x6809b9a 109091738 (96: nrow: 246 rrow: 246) leaf: 0x6809bd6 109091798 (97: nrow: 246 rrow: 246) leaf: 0x6809bd2 109091794 (98: nrow: 246 rrow: 246)----- end tree dump index full scan 读取的是 0x6c07ea0 这个块 读取顺序：首先定位到索引的root block、然后到branch block（如果有的话）、再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。结论：它所读取的块都是有顺序的，也是经过排序的。FS 数据应该是从头开始的 index fast full scan读取的是 0x6809b9a 这个块 读取顺序： 从段头开始，读取包含位图块、root block、所有的branch block、leaf block,读取的顺序完全有物理存储位置决定，并采取多块读，每次读取db_file_multiblock_read_count个块结论：也就是包含数据的物理存储位置最前的块。 FFS数据应该不是从头开始的 --9.3 分别看一下这两个块的内容FS: 0x6c07ea0 =十进制的113278624 FFS:0x6809b9a =十进制的109091738 SQL> select dbms_utility.data_block_address_file(113278624) "file",dbms_utility.data_block_address_block(113278624) "block" from dual; file block---------- ---------- 27 32416FS: 文件号 27 快号 32416 SQL> select dbms_utility.data_block_address_file(109091738) "file",dbms_utility.data_block_address_block(109091738)"block" from dual; file block---------- ---------- 26 39834FFS: 文件号 26 块号 39834--9.4 dump该数据块SQL> alter system dump datafile 26 block 39834;block 39834的前10行row#0[4591] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 43col 1; len 6; (6): 02 81 71 f6 00 36row#1[4605] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 44col 1; len 6; (6): 02 81 71 f6 00 35row#2[4619] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 45col 1; len 6; (6): 02 81 71 f6 00 34row#3[4633] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 46col 1; len 6; (6): 02 81 71 f6 00 33row#4[4647] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 47col 1; len 6; (6): 02 81 71 f6 00 32row#5[4661] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 48col 1; len 6; (6): 02 81 71 f6 00 31row#6[4675] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 49col 1; len 6; (6): 02 81 71 f6 00 30row#7[4689] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 4acol 1; len 6; (6): 02 81 71 f6 00 2frow#8[4703] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 4bcol 1; len 6; (6): 02 81 71 f6 00 2erow#9[4717] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 4ccol 1; len 6; (6): 02 81 71 f6 00 2dSQL> alter system dump datafile 27 block 32416;block 32416的前10行 row#0[6564] flag: -----, lock: 2col 0; len 4; (4): c3 02 07 11col 1; len 6; (6): 07 00 7c 20 00 2brow#1[6578] flag: -----, lock: 2col 0; len 4; (4): c3 02 16 4ecol 1; len 6; (6): 07 00 7c 20 00 2arow#2[6592] flag: -----, lock: 2col 0; len 4; (4): c3 02 16 4fcol 1; len 6; (6): 07 00 7c 20 00 29row#3[6606] flag: -----, lock: 2col 0; len 4; (4): c3 02 16 50col 1; len 6; (6): 07 00 7c 20 00 28row#4[6620] flag: -----, lock: 2col 0; len 4; (4): c3 02 18 02col 1; len 6; (6): 07 00 7c 20 00 27row#5[6634] flag: -----, lock: 2col 0; len 4; (4): c3 02 23 60col 1; len 6; (6): 07 00 7c 20 00 26row#6[6648] flag: -----, lock: 2col 0; len 4; (4): c3 02 24 25col 1; len 6; (6): 07 00 7c 20 00 25row#7[6662] flag: -----, lock: 2col 0; len 4; (4): c3 02 24 28col 1; len 6; (6): 07 00 7c 20 00 24row#8[6676] flag: -----, lock: 2col 0; len 4; (4): c3 02 28 18col 1; len 6; (6): 07 00 7c 20 00 23row#9[6690] flag: -----, lock: 2col 0; len 4; (4): c3 02 42 04col 1; len 6; (6): 07 00 7c 20 00 22 --9.5 对照一下前面的结果集block 32416的第一行为10616,数据内的存储格式应该为 SQL> select dump(10616,16) from dual;DUMP(10616,16)----------------------Typ=2 Len=4: c3,2,7,11确实等于dump block所看到的 row#0[6564] flag: -----, lock: 2col 0; len 4; (4): c3 02 07 11col 1; len 6; (6): 07 00 7c 20 00 2b再看block 39834的第1行 SQL> select dump(66266,16) from dual;DUMP(66266,16)-----------------------Typ=2 Len=4: c3,7,3f,43跟dump 的结果也一样 row#0[4591] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 43col 1; len 6; (6): 02 81 71 f6 00 36--9.6 结论：证明了上面所说的index full scan和index fast full scan的不同。--10.我们也可以用10046事件去跟踪两者走的路径--10.1.1 清空buffer_cacheSQL> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';（清空buffer cache,以便观看'db file sequential read','db file scattered read'事件）。 --10.1.2 打开10046事件SQL> alter session set events'10046 trace name context forever,level 12';Session altered.--10.1.3 FFS 结果SQL> select object_id from test where rownum<11; OBJECT_ID---------- 66266 66267 66268 66269 66270 66271 66272 66273 66274 6627510 rows selected.--10.1.4 关闭10046事件SQL> alter session set events'10046 trace name context off';Session altered.--10.1.5 观察该 FFS 的 trc文件 [oracle@csdbc udump]$ grep read cs-dbc_ora_15596.trc Redo thread mounted by this instance: 1WAIT #1: nam='db file sequential read' ela= 33 p1=26 p2=39820 p3=1WAIT #1: nam='db file sequential read' ela= 21 p1=26 p2=39817 p3=1WAIT #1: nam='db file sequential read' ela= 17 p1=26 p2=39819 p3=1WAIT #1: nam='db file parallel read' ela= 53 p1=2 p2=2 p3=2WAIT #1: nam='db file scattered read' ela= 466 p1=26 p2=39821 p3=16最前面的'db file sequential read'是由于读段头等操作，我们来关注'db file scattered read'事件，因为index fast full scan是采用多块读，从39821开始读取db_file_multiblock_read_count个块（本例里设置为16）。我们关心的39834块正位于其中。--10.2 再来看index full scan的10046 trace --10.2.1 清空buffer cacheSQL> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';（清空buffer cache,以便观看'db file sequential read','db file scattered read'事件）。 --10.2.2 打开10046事件 SQL> alter session set events'10046 trace name context forever,level 12';Session altered.--10.2.3 FS结果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.--10.2.4 关闭10046事件SQL> alter session set events'10046 trace name context off';Session altered.--10.2.5 观察该 FS 的 trc文件 [oracle@csdbc udump]$ grep read cs-dbc_ora_15609.trcRedo thread mounted by this instance: 1WAIT #1: nam='db file sequential read' ela= 49 p1=26 p2=39821 p3=1root block,正是先前索引树dump里面的 0x6809b8d WAIT #1: nam='db file sequential read' ela= 32 p1=26 p2=39830 p3=1WAIT #1: nam='db file sequential read' ela= 40 p1=27 p2=32449 p3=1WAIT #1: nam='db file sequential read' ela= 35 p1=27 p2=32445 p3=1WAIT #1: nam='db file sequential read' ela= 28 p1=27 p2=32433 p3=1WAIT #1: nam='db file sequential read' ela= 19 p1=27 p2=32429 p3=1WAIT #1: nam='db file sequential read' ela= 34 p1=27 p2=32425 p3=1WAIT #1: nam='db file sequential read' ela= 32 p1=27 p2=32421 p3=1WAIT #1: nam='db file sequential read' ela= 33 p1=27 p2=32417 p3=1WAIT #1: nam='db file sequential read' ela= 29 p1=27 p2=32413 p3=1WAIT #1: nam='db file sequential read' ela= 37 p1=27 p2=32409 p3=1WAIT #1: nam='db file sequential read' ela= 32 p1=27 p2=32405 p3=1WAIT #1: nam='db file sequential read' ela= 35 p1=27 p2=32401 p3=1WAIT #1: nam='db file sequential read' ela= 34 p1=27 p2=32397 p3=1WAIT #1: nam='db file sequential read' ela= 31 p1=27 p2=32456 p3=1WAIT #1: nam='db file sequential read' ela= 29 p1=27 p2=32452 p3=1WAIT #1: nam='db file sequential read' ela= 31 p1=27 p2=32448 p3=1WAIT #1: nam='db file sequential read' ela= 30 p1=27 p2=32444 p3=1WAIT #1: nam='db file sequential read' ela= 38 p1=26 p2=39858 p3=1WAIT #1: nam='db file sequential read' ela= 31 p1=27 p2=32440 p3=1WAIT #1: nam='db file sequential read' ela= 32 p1=27 p2=32436 p3=1WAIT #1: nam='db file sequential read' ela= 35 p1=27 p2=32432 p3=1WAIT #1: nam='db file sequential read' ela= 31 p1=27 p2=32428 p3=1WAIT #1: nam='db file sequential read' ela= 29 p1=26 p2=39854 p3=1WAIT #1: nam='db file sequential read' ela= 36 p1=27 p2=32424 p3=1WAIT #1: nam='db file sequential read' ela= 32 p1=27 p2=32420 p3=1WAIT #1: nam='db file sequential read' ela= 36 p1=27 p2=32416 p3=1--10.3 结论index full scan走的路径正是文章开始所提到的定位到root block，然后根据leaf block链表一路读取块。 看到这里大家应该比较了解index full scan 和index fast full scan的区别了。--11. 最后补充一下 index full scan 和 index fast full scan 在排序上的不同。 --11.1打开trace跟踪SQL> set autotrace trace;--11.2 FS 结果验证SQL> select object_id from test order by object_id;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)由于有排序所以oracle自动选择了index full scan避免了排序。那么强制用index fast full scan呢？ --11.3 FFS结果验证SQL> select/*+ index_ffs(test ind_test_id)*/object_id from test order by object_id;17837 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=17837 Bytes=71348) 1 0 SORT (ORDER BY) (Cost=59 Card=17837 Bytes=71348) 2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)结论：index full scan 会自动的执行 sort (order by)index fast full scan会多一步 sort (order by) --------------------------------------------------------------------------------本文原作者WangHai(要了解他请访问他的Blog). 本文版权归原作者所有。转载请注明作者及出处！
----- begin tree dumpbranch: 0x10000f3 16777459 (0: nrow: 255, level: 1) leaf: 0x10000f7 16777463 (-1: nrow: 289 rrow: 289) leaf: 0x100447f 16794751 (0: nrow: 246 rrow: 246) leaf: 0x100447b 16794747 (1: nrow: 246 rrow: 246) leaf: 0x1004477 16794743 (2: nrow: 246 rrow: 246) leaf: 0x1004473 16794739 (3: nrow: 246 rrow: 246) leaf: 0x100446f 16794735 (4: nrow: 246 rrow: 246) leaf: 0x100446b 16794731 (5: nrow: 246 rrow: 246) . . . leaf: 0x100024c 16777804 (249: nrow: 246 rrow: 246) leaf: 0x100024b 16777803 (250: nrow: 251 rrow: 251) leaf: 0x10000f6 16777462 (251: nrow: 246 rrow: 246) leaf: 0x10000f5 16777461 (252: nrow: 246 rrow: 246) leaf: 0x10000f4 16777460 (253: nrow: 246 rrow: 246)----- end tree dump ####################################################################结构说明:branch：表示该数据块是 枝节点数据块leaf: 表示该数据块是 叶节点数据块0x10000f3: 对应当前数据块的十六进制地址16777459：对应当前数据块的十进制地址-1: 表示当前数据块的编号，编号起始是-1。以上图示 该索引共包含 1个枝节点数据块， 253 - (0)- (-1) =255个叶子节点数据块nrow: 289 ：表示该索引数据块中总的行数，包含被删除的行rrow: 289 ：表示该索引数据块中实际存在有效行数####################################################################表示共有255个索引块branch:表示为根节点 1 个leaf:表示为叶子节点 255 个.SQL> SELECT NAME, BLOCKS, HEIGHT,LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, BTREE_SPACE, USED_SPACE FROM INDEX_STATS WHERE NAME='IND_TEST_ID';NAME BLOCKS HEIGHT LF_ROWS LF_BLKS BR_ROWS BR_BLKS BTREE_SPACE USED_SPACE------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------IND_TEST_ID 384 2 62862 255 254 1 2047008 1008179--完全符合以上 dump的结果SQL> select dbms_utility.data_block_address_file(16777459),dbms_utility.data_block_address_block(16777459) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777459) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777459)---------------------------------------------- ----------------------------------------------- 4 243243数据块 无从验证dump 枝节点Branch block dump=================结构说明:header address 140343647131724=0x7fa44d3bd84ckdxcolev 1 该节点到叶节点的深度(index level, 0表示是leaf block 1 表示是 branch block)KDXCOLEV Flags = - - - is converted=Y 内部操作代码kdxcolok 1 是否有事务lock了这个branch block，如果有 有多少事务kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Ykdxconco 2 索引列数量 ？？？kdxcosdc 1 索引结构改变次数kdxconro 254 索引记录数量BR_ROWSkdxcofbo 536=0x218 空闲空间开始偏移量kdxcofeo 5542=0x15a6 空闲空间结束偏移量kdxcoavs 5006 所提供的空闲空间kdxbrlmc 16777463=0x10000f7kdxbrsno 0 最后一次被更改的索引条目kdxbrbksz 8056 使用的数据块空间kdxbr2urrc 0kdxlespl 0 在数据块被cleaned out时，还未进行commit的数量kdxlende 122 被删除索引记录的数量kdxlenxt 67120396=0x4002d0c 下一个链接数据块的地址kdxleprv 0=0x0 上一个链接数据块地址kdxledsz 0 被删除的空间大小kdxlebksz 8036 使用的数据块空间其他注释kdxcolev: index level (0 represents leaf blocks)kdxcolok: denotes whether structural block transaction is occurringkdxcoopc: internal operation codekdxconco: index column countkdxcosdc: count of index structural changes involving blockkdxconro: number of index entries (does not include kdxbrlmc pointer)kdxcofbo: offset to beginning of free space within blockkdxcofeo: offset to the end of free space (ie. first portion of block containing index data)kdxcoavs: available space in block (effectively area between the two fields above)http://blog.csdn.net/robinson1988/article/details/7312683?locationNum=1&fps=1
转载于:https://www.cnblogs.com/iyoume2008/p/6027489.html
