ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于insert操作造成索引叶节点分裂的验证

关于insert操作造成索引叶节点分裂的验证

原创 Linux操作系统 作者:lawzjf 时间:2006-04-30 00:00:00 0 删除 编辑

建立测试表及索引:

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)--保证索引键值添加到第一个叶节点

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 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

再查看添加数据前后的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 -----


我们看到在原来的基础上多出了0x1000043(即67)号数据块,很明显这是Oracle新分配的数据块。

我们导出原来的65号及新的67号数据块内容,同样可以看到两个块平分了原来65号数据块的内容。
结论:
当向一个没有空余空间的索引叶节点添加键值时,不管其下一个相邻的数据块有无空余空间,都会造成新块的分配。原来数据块中的内容大体会在这两个数据块中平分。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37724/viewspace-152535/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-18

  • 博文量
    279
  • 访问量
    933087