oracle B*Tree索引的理解

ptajxcqvnd

原文http://www.itpub.net/237710.html，在这篇文章的基础上重做了一下实验。深入了解B*Tree的内部结构。　　1 索引的root, brance和leaf
　　1.1 新建表, 索引, 新增数据
　　SQL> create tablespace ASSM datafile '/oradata/ltest/assm.dbf' size 10m autoextend on segment space management auto;
　　Tablespace created
　　SQL> create tablespace ASSM_INDEX datafile '/oradata/ltest/assm_index.dbf' size 10m autoextend on segment space management auto;
　　Tablespace created
　　SQL> create table t(x char(1024)) tablespace assm;
　　Table created
　　SQL> create index ti on t(x) tablespace assm_index;
　　Index created
　　SQL> insert into t values(1);
　　1 row inserted
　　SQL> commit;
　　Commit complete
　　1.2 dump索引数据
　　SQL> select file_id, extent_id, block_id, blocks
　　 2 from dba_extents
　　 3 where segment_name = 'TI';
　　 FILE_ID EXTENT_ID BLOCK_ID BLOCKS
　　---------- ---------- ---------- ----------
　　 10 0 9 8
　　因为位图块是9和10，段头块是11，所以dump的块要是12。
　　SQL> alter system dump datafile 10 block 12;
　　System altered
　　Trace信息如下：
　　row#0[6997] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024): '下面是indexed data value(1024Byte)，第一个31代表1'
　　31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　…… 剩下的都是20，即是空格。共1023个空格。
　　col 1; len 6; (6): 01 c0 00 10 00 00'这个值是对应row在表中的RowID(6Byte)'
　　1.3 索引数据验证
　　利用dbms_rowid包从到数据的FILE#和BLOCK。
　　SQL> select a.*,
　　 2 rowid,
　　 3 dbms_rowid.rowid_object(rowid) obj#,
　　 4 dbms_rowid.rowid_relative_fno(rowid) file#,
　　 5 dbms_rowid.rowid_block_number(rowid) block#,
　　 6 dbms_rowid.rowid_row_number(rowid) row#
　　 7 from t a;
　　X ROWID OBJ# FILE# BLOCK# ROW#
　　---------- ------------------ ---------- ---------- ---------- ----------
　　1 AAANC3AAHAAAAAQAAA 53431 7 16 0
　　通过DUMP中信息，得到索引信息01 c0 00 10 00 00。取前4个字节计算FILE#和BLOCK#。后2个字节是用来算ROW#的。
　　SQL> select dbms_utility.data_block_address_file(to_number('01c00010', 'xxxxxxxx')) file#,
　　 2 dbms_utility.data_block_address_block(to_number('01c00010', 'xxxxxxxx')) block#
　　 3 from dual;
　　 FILE# BLOCK#
　　---------- ----------
　　 7 16
　　验证：索引信息指向对应数据行。（ROW#明显是一样的，就不验证了）
　　1.4 计算索引块能存储的数据行
　　index leaf node包含很多的index entry (也就是row), 每个entry有5个columns：
　　row header(3Byte) | length(1 Byte) | indexed data value(1024 Byte) | length(1 Byte) | RowID(6 Byte)
　　这样每个row的大小为：3 + 1 + 1024 + 1 + 6=1035 Byte。
　　db_block_size=8192，block的默认pct_free=10%，所以每个block能存储7个rows:
　　SQL> select 8192*0.9/1035 from dual;
　　8192*0.9/1035
　　-------------
　　7.12347826086
　　1.5 继续插入数据
　　继续插入数据
　　SQL> insert into t values(10);
　　1 row inserted
　　SQL> commit;
　　Commit complete
　　SQL> insert into t values(2);
　　1 row inserted
　　SQL> insert into t values(3);
　　1 row inserted
　　SQL> insert into t values(4);
　　1 row inserted
　　SQL> insert into t values(5);
　　1 row inserted
　　SQL> insert into t values(6);
　　1 row inserted
　　SQL> commit;
　　Commit complete
　　
　　SQL> analyze index ti validate structure;
　　Index analyzed
　　SQL> select btree_space, used_space, pct_used, blocks, lf_blks, br_blks
　　 2 from index_stats;
　　BTREE_SPACE USED_SPACE PCT_USED BLOCKS LF_BLKS BR_BLKS
　　----------- ---------- ---------- ---------- ---------- ----------
　　 7996 6222 78 8 1 0
　　只有一个leaf node,也就是root, 没有更多的branch node
　　1.6 dump索引数据
　　SQL> alter system dump datafile 10 block 12;
　　System altered
　　Trace信息如下：
　　row#0[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31代表1'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 00
　　row#1[5962] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31 30代表10'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 01
　　row#2[4927] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '32代表2'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 02
　　row#3[3892] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '33代表3'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 03
　　row#4[2857] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　34 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '34代表4'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 04
　　row#5[1822] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　35 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '35代表5'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 05
　　row#6[787] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　36 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '36代表6'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 06
　　1.7 第一个索引块满后，再插入数据
　　SQL> insert into t values(7);
　　1 row inserted
　　SQL> commit;
　　Commit complete
　　SQL> analyze index ti validate structure;
　　Index analyzed
　　SQL> select btree_space, used_space, pct_used, blocks, lf_blks, br_blks
　　 2 from index_stats;
　　BTREE_SPACE USED_SPACE PCT_USED BLOCKS LF_BLKS BR_BLKS
　　----------- ---------- ---------- ---------- ---------- ----------
　　 24020 8305 35 8 2 1
　　发现BTREE_SPACE的值是3个blocks的大小了，其中有2个是leaf nodes，1个是branch node，也就是root，它单独占一个block；因为这两个leaf nodes不能只是独自地排列在那里，所以有了上一层的branch(root) node来管理他们；如果你仔细一点会发现，这个root占用的block是最开始的那个leaf占用的block 12，并不是为它新开辟了一个block，而是将原来那个leaf中的entries转移到后面的block(13)中去了。同时新插入的第8行index entry，放在了新的block 14中去了。之所以将第一个leaf转移到后面的block，而将这个block作为root，是因为root的信息保存在数据字典中，修改数据字典的代价相比移动leaf要大一些。
　　1.8 验证上面的结论
　　SQL> alter system dump datafile 10 block 12;
　　System altered
　　Trace信息如下：
　　row#0[8049] dba: 41943054=0x280000e
　　col 0; len 1; (1): 37 '--37代表7，即最大值'
　　col 1; TERM
　　SQL> alter system dump datafile 10 block 13;
　　System altered
　　Trace信息如下：
　　row#0[787] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 00
　　row#1[1822] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 01
　　row#2[2857] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 02
　　row#3[3892] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 03
　　row#4[4927] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　34 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 04
　　row#5[5962] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　35 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 05
　　row#6[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　36 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 06
　　观察trace文件，发现row的顺序没有变，但是物理偏移量正好和原来相反了，应该是转移这个块的结果。移动前：
　　row#0[6997] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 1
　　row#1[5962] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 10
　　row#2[4927] flag: ------, lock: 2, len=1035 col 0; len 1024; (1024): 2
　　row#3[3892] flag: ------, lock: 2, len=1035 col 0; len 1024; (1024): 3
　　row#4[2857] flag: ------, lock: 2, len=1035 col 0; len 1024; (1024): 4
　　row#5[1822] flag: ------, lock: 2, len=1035 col 0; len 1024; (1024): 5
　　row#6[787] flag: ------, lock: 2, len=1035 col 0; len 1024; (1024): 6
　　移动后：
　　row#0[787] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 1
　　row#1[1822] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 10
　　row#2[2857] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 2
　　row#3[3892] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 3
　　row#4[4927] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 4
　　row#5[5962] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 5
　　row#6[6997] flag: ------, lock: 0, len=1035 col 0; len 1024; (1024): 6
　　
　　SQL> alter system dump datafile 10 block 14;
　　System altered
　　Trace信息如下：
　　row#0[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　37 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 0c 00 00
　　2 索引的分裂
　　如果一个leaf block已经满了（例如block 12），此时又产生了新的entry，按照他的indexed value应该插入到block 13当中，分析一下这种情况：
　　此时block 13中的值是：1，10，2，3，4，5，6
　　因为是index 是char数据类型，如果再插入一行41，按照char的排序规则，41应该插入到4和5之间。这种情况下，会将block 13中的内容分裂成两部分，41数据后的entry将移到一个新的block中去。这样的话，应该是：
　　block 12: root
　　block 13: 1，10，2，3，4，41
　　block 14: 7
　　block 15: 5，6
　　2.1 插入数据，引用索引分裂
　　SQL> insert into t values(41);
　　1 row inserted
　　SQL> commit;
　　Commit complete
　　SQL> analyze index ti validate structure;
　　Index analyzed
　　SQL> select btree_space, used_space, pct_used, blocks, lf_blks, br_blks
　　 2 from index_stats;
　　BTREE_SPACE USED_SPACE PCT_USED BLOCKS LF_BLKS BR_BLKS
　　----------- ---------- ---------- ---------- ---------- ----------
　　 32016 9351 30 8 3 1
　　此时4个blocks，1个root，3个leaf。
　　2.2 Dump索引数据块，验证
　　SQL> alter system dump datafile 10 block 13;
　　System altered
　　Trace信息如下：
　　row#0[1822] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31代表1'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 00
　　row#1[2857] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31 30代表10'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 01
　　row#2[3892] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '32代表2'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 02
　　row#3[4927] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '33代表3'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 03
　　row#4[5962] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　34 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '34代表4'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 04
　　row#5[6997] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　34 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '34 31代表41'
　　……
　　col 1; len 6; (6): 01 c0 00 0c 00 01
　　SQL> alter system dump datafile 10 block 15;
　　System altered
　　Trace信息如下：
　　row#0[5962] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　35 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '35代表5'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 05
　　row#1[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　36 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '36代表6'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 06
　　3 索引的重用
　　所谓重用，是对row 的重用，而不是对row所在物理存储（或说物理位置）的重用。索引是按照indexed value对row进行排序的。有新的row被插入，首先按照value排序，将他放在合适的row list中，如果他的位置正好原来有个row被删掉了，则重用这个row在row list中的位置。但是物理存储是依次向下开辟的。看看下面这个例子，删除4，插入31，31重用了原来4在row list中的位置，但是物理存储是新开辟的:
　　3.1 删数据
　　SQL> delete t where x = 4;
　　1 row deleted
　　SQL> commit;
　　Commit complete
　　3.2 Dump数据块
　　SQL> alter system dump datafile 10 block 13;
　　System altered
　　Trace信息如下
　　row#0[1822] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31代表1'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 00
　　row#1[2857] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31 30代表10'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 01
　　row#2[3892] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '32代表2'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 02
　　row#3[4927] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '33代表3'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 03
　　row#4[5962] flag: ---D--, lock: 2, len=1035 '有D，表示删除'
　　col 0; len 1024; (1024):
　　34 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '34代表4'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 04
　　row#5[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　34 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '34 31代表41'
　　……
　　col 1; len 6; (6): 01 c0 00 0c 00 01
　　3.3 插入数据
　　SQL> insert into t values(31);
　　1 row inserted
　　SQL> commit;
　　Commit complete
　　3.4 再Dump数据块
　　SQL> alter system dump datafile 10 block 13;
　　System altered
　　Trace信息如下：
　　row#0[1822] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31代表1'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 00
　　row#1[2857] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '31 30代表10'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 01
　　row#2[3892] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '32代表2'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 02
　　row#3[4927] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '33代表3'
　　……
　　col 1; len 6; (6): 01 c0 00 10 00 03
　　row#4[787] flag: ------, lock: 2, len=1035
　　col 0; len 1024; (1024):
　　33 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20'33 31代表31, 重用了原来4在row list 中的位置，但是物理存储是新的787，原来是5962'
　　……
　　col 1; len 6; (6): 01 c0 00 0c 00 02
　　row#5[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　34 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 '34 31代表41'
　　……
　　col 1; len 6; (6): 01 c0 00 0c 00 01
　　
　　SQL> alter system dump datafile 10 block 14;
　　System altered
　　Trace内容如下：
　　Leaf block dump
　　===============
　　header address 105839716=0x64efc64
　　kdxcolev 0
　　KDXCOLEV Flags = - - -
　　kdxcolok 1
　　kdxcoopc 0x89: opcode=9: iot flags=--- is converted=Y
　　kdxconco 2
　　kdxcosdc 1
　　kdxconro 1
　　kdxcofbo 38=0x26
　　kdxcofeo 6997=0x1b55
　　kdxcoavs 6959
　　kdxlespl 0
　　kdxlende 0
　　kdxlenxt 0=0x0下一个索引块的地址为0，说明是最后一个索引块
　　kdxleprv 41943055=0x280000f上一个索引块的地址为0x280000f（对应块15）
　　kdxledsz 0
　　kdxlebksz 8032
　　row#0[6997] flag: ------, lock: 0, len=1035
　　col 0; len 1024; (1024):
　　37 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
　　……
　　col 1; len 6; (6): 01 c0 00 0c 00 00