关于insert操作造成索引叶节点分裂的验证-优快云博客

建立测试表及索引：

SQL> create table testidx
2 as
3 select * from all_objects
4 where rownum<3000
5 /

表已创建。

SQL> select count(*) from testidx;

COUNT(*)
----------
2999

SQL> create index idx on testidx(object_id)
2 pctfree 0--以便除最后一个叶节点外，其他叶节点都被充满
3 /

索引已创建。

SQL> col segment_name for a10

SQL> select segment_name,file_id,extent_id,block_id
2 from dba_extents
3 where segment_name='IDX'
4 /

SEGMENT_NA FILE_ID EXTENT_ID BLOCK_ID
---------- ---------- ---------- ----------
IDX 4 0 57
IDX 4 1 65

导出现在(添加数据前)的root节点及两个叶节点：

SQL> alter system dump datafile 4 block min 60 block max 62;

系统已更改。

root节点的导出内容：

Branch block dump
=================
header address 122495564=0x74d224c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 5
kdxcofbo 38=0x26
kdxcofeo 8015=0x1f4f
kdxcoavs 7977
kdxbrlmc 16777277=0x100003d
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8051] dba: 16777278=0x100003e
col 0; len 3; (3): c2 07 0a
col 1; TERM
row#1[8042] dba: 16777279=0x100003f
col 0; len 3; (3): c2 0c 41
col 1; TERM
row#2[8033] dba: 16777280=0x1000040
col 0; len 3; (3): c2 11 62
col 1; TERM
row#3[8024] dba: 16777281=0x1000041
col 0; len 3; (3): c2 17 1f
col 1; TERM
row#4[8015] dba: 16777282=0x1000042
col 0; len 3; (3): c2 1c 40
col 1; TERM
----- end of branch block dump -----

我们通过下面对表的查询确认一下第一个叶节点中数据的范围(当然这里也可以直接导出第一个叶节点的内容来查看)，从而确定我们要添加的索引键值的范围：

SQL> select object_id from testidx
2 where object_id<300
3 /

OBJECT_ID
----------
2
3
4
5

……

先把测试表的not null约束删除，然后添加一条记录，使得索引键值添加到第一个叶节点：

SQL> alter table testidx modify owner null;

表已更改。

SQL> alter table testidx modify object_name null;

表已更改。

SQL> alter table testidx modify created null;

表已更改。

SQL> alter table testidx modify LAST_DDL_TIME null;

表已更改。

SQL> insert into testidx(object_name,object_id)
2 values('LAW',3)--保证索引键值添加到第一个叶节点

已创建 1 行。

我们再重新导出root节点：

SQL> alter system dump datafile 4 block 60;

系统已更改。

添加数据后的root节点内容如下：
Branch block dump
=================
header address 118497868=0x710224c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 6
kdxcofbo 40=0x28
kdxcofeo 8006=0x1f46
kdxcoavs 7966
kdxbrlmc 16777277=0x100003d
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8006] dba: 16777286=0x1000046--新分配数据块
col 0; len 3; (3): c2 03 64col 1; TERM
row#1[8051] dba: 16777278=0x100003e
col 0; len 3; (3): c2 07 0a
col 1; TERM
row#2[8042] dba: 16777279=0x100003f
col 0; len 3; (3): c2 0c 41
col 1; TERM
row#3[8033] dba: 16777280=0x1000040
col 0; len 3; (3): c2 11 62
col 1; TERM
row#4[8024] dba: 16777281=0x1000041
col 0; len 3; (3): c2 17 1f
col 1; TERM
row#5[8015] dba: 16777282=0x1000042
col 0; len 3; (3): c2 1c 40
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 60 maxblk 60

再查看添加数据前后的61号节点
添加数据前：
Leaf block dump
===============
header address 122495588=0x74d2264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 540
kdxcofbo 1116=0x45c
kdxcofeo 1120=0x460
kdxcoavs 4
kdxlespl 0 ##bytes of uncommitted data at time of block split that have been cleaned out
kdxlende 0 ##number of deleted entries
kdxlenxt 16777278=0x100003e--下一个叶节点，键值添加后请注意这里的变化
kdxleprv 0=0x0 ##pointer to the previous leaf block in the index structure via corresponding rba
kdxledsz 0
kdxlebksz 8036 ##usable block space (by default less than branch due to the additional ITL entry)
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 00 0c 00 2d
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 00 00 0c 00 05
row#2[8000] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 00 00 0c 00 2e
row#3[7988] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 00 00 0c 00 19
row#4[7976] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 00 00 0c 00 14
row#5[7964] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 00 00 0c 00 10
……
row#534[1185] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 04
col 1; len 6; (6): 01 00 00 12 00 0e
row#535[1172] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 05
col 1; len 6; (6): 01 00 00 12 00 0f
row#536[1159] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 06
col 1; len 6; (6): 01 00 00 12 00 10
row#537[1146] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 07
col 1; len 6; (6): 01 00 00 12 00 11
row#538[1133] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 08
col 1; len 6; (6): 01 00 00 12 00 12
row#539[1120] flag: ------, lock: 0, len=13--一共540个键值，键值添加后请注意这里的变化col 0; len 3; (3): c2 07 09
col 1; len 6; (6): 01 00 00 12 00 13
----- end of leaf block dump -----

添加数据后：
Leaf block dump
===============
header address 118497892=0x7102264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 279
kdxcofbo 594=0x252
kdxcofeo 4510=0x119e
kdxcoavs 3916
kdxlespl 0
kdxlende 0
kdxlenxt 16777286=0x1000046--指向的下一个叶节点已经改为新分配到的数据块
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[4510] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 00 0c 00 2d
row#1[4522] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 00 00 0c 00 05
row#2[4534] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 00 00 36 00 00
row#3[4546] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 00 00 0c 00 2e
row#4[4558] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 00 00 0c 00 19
row#5[4570] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 00 00 0c 00 14
……
row#275[7984] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 03 5e
col 1; len 6; (6): 01 00 00 0f 00 09
row#276[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 03 61
col 1; len 6; (6): 01 00 00 0f 00 0a
row#277[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 03 62
col 1; len 6; (6): 01 00 00 0f 00 0b
row#278[8023] flag: ------, lock: 0, len=13--剩余到此块的键值大约为原来的一半

col 0; len 3; (3): c2 03 63
col 1; len 6; (6): 01 00 00 0f 00 0c
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 61 maxblk 61

再查看新分配的70号数据块中的数据：
row#0[4634] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 03 64
col 1; len 6; (6): 01 00 00 0f 00 0d
row#1[4647] flag: ------, lock: 0, len=12
col 0; len 2; (2): c2 04
col 1; len 6; (6): 01 00 00 0f 00 0e
row#2[4659] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 04 02
col 1; len 6; (6): 01 00 00 0f 00 0f
……
row#259[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 07
col 1; len 6; (6): 01 00 00 12 00 11
row#260[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 08
col 1; len 6; (6): 01 00 00 12 00 12
row#261[8023] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 09
col 1; len 6; (6): 01 00 00 12 00 13
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 70 maxblk 70

结论：大体是新旧数据块把原来数据块中的数据平分

我们向倒数第二个块加数据，显然这时最后一个叶节点是有剩余空间的，我们看这时，是否会有新块分配出来。

在加入数据前的root节点如下：

Branch block dump
=================
header address 121512524=0x73e224c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 6
kdxcofbo 40=0x28
kdxcofeo 8006=0x1f46
kdxcoavs 7966
kdxbrlmc 16777277=0x100003d
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8006] dba: 16777286=0x1000046
col 0; len 3; (3): c2 03 64
col 1; TERM
row#1[8051] dba: 16777278=0x100003e
col 0; len 3; (3): c2 07 0a
col 1; TERM
row#2[8042] dba: 16777279=0x100003f
col 0; len 3; (3): c2 0c 41
col 1; TERM
row#3[8033] dba: 16777280=0x1000040
col 0; len 3; (3): c2 11 62
col 1; TERM
row#4[8024] dba: 16777281=0x1000041
col 0; len 3; (3): c2 17 1f
col 1; TERM
row#5[8015] dba: 16777282=0x1000042
col 0; len 3; (3): c2 1c 40
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 60 maxblk 60

导出0x1000041，即第65号数据块：

kdxlenxt 16777282=0x1000042
kdxleprv 16777280=0x1000040
kdxledsz 0
kdxlebksz 8036
row#0[8023] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 17 1f
col 1; len 6; (6): 01 00 00 26 00 4e
row#1[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 17 20
col 1; len 6; (6): 01 00 00 27 00 00
……
col 0; len 3; (3): c2 1c 3e
col 1; len 6; (6): 01 00 00 2e 00 26
row#532[1112] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 1c 3f
col 1; len 6; (6): 01 00 00 2e 00 27
----- end of leaf block dump -----
可以看出此块键值的范围是c2 17 1f到c2 1c 3f即2230到2752。
我们向表添加记录，使得索引列的值为2340，从而保证其落入65号数据块。

SQL> insert into testidx(owner,object_id)
2 values('ddd',2340)
3 /

已创建 1 行。

然后我们重新导出root节点：

SQL> alter system dump datafile 4 block 60;

系统已更改。

导出内容为：
Branch block dump
=================
header address 121512524=0x73e224c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 42=0x2a
kdxcofeo 7998=0x1f3e
kdxcoavs 7956
kdxbrlmc 16777277=0x100003d
kdxbrsno 5
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8006] dba: 16777286=0x1000046
col 0; len 3; (3): c2 03 64
col 1; TERM
row#1[8051] dba: 16777278=0x100003e
col 0; len 3; (3): c2 07 0a
col 1; TERM
row#2[8042] dba: 16777279=0x100003f
col 0; len 3; (3): c2 0c 41
col 1; TERM
row#3[8033] dba: 16777280=0x1000040
col 0; len 3; (3): c2 11 62
col 1; TERM
row#4[8024] dba: 16777281=0x1000041
col 0; len 3; (3): c2 17 1f
col 1; TERM
row#5[7998] dba: 16777283=0x1000043 -- 又分配了新块
col 0; len 2; (2): c2 1a
col 1; TERM
row#6[8015] dba: 16777282=0x1000042
col 0; len 3; (3): c2 1c 40
col 1; TERM
----- end of branch block dump -----