關於insert操作造成索引葉節點分裂的驗證

lawzjf發表於2006-04-30

建立測試表及索引:

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章