關於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)--保證索引鍵值新增到第一個葉節點
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 -----
我們看到在原來的基礎上多出了0x1000043(即67)號資料塊，很明顯這是Oracle新分配的資料塊。
我們匯出原來的65號及新的67號資料塊內容，同樣可以看到兩個塊平分了原來65號資料塊的內容。
結論：
當向一個沒有空餘空間的索引葉節點新增鍵值時，不管其下一個相鄰的資料塊有無空餘空間，都會造成新塊的分配。原來資料塊中的內容大體會在這兩個資料塊中平分。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/37724/viewspace-152535/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 索引分裂造成的index contention等待事件的診斷索引Index事件
- 【ASK_ORACLE】關於Oracle索引分裂你需要知道的Oracle索引
- 索引分裂的enq索引ENQ
- 關於唯一性索引造成堵塞和非唯一性索引造成堵塞的區別索引
- jQuery關於DOM操作節點一些方法jQuery
- 關於insert /* append */的幾點註記APP
- mysql根據節點查詢所有葉節點MySql
- 關於Java Mail的身份驗證！JavaAI
- MySQL:非葉子節點指標MySql指標
- 關於值物件的驗證的問題物件
- 使用 Java 操作 Git-驗證相關JavaGit
- Oracle索引分裂（Index Block Split）Oracle索引IndexBloC
- 關於憑證的修改和沖銷操作
- 如何證明INNODB輔助索引葉子結點KEY值相同的按照PRIMARY KEY排序索引排序
- 關於MFC操作Excel的幾點心得Excel
- 關於用java生成驗證碼Java
- 為什麼二叉樹中葉子節點個數等於度為2的節點個數+1二叉樹
- 關於Presentation Error的一點經驗Error
- mysql 關於主鍵索引--節選丁奇部落格MySql索引
- LeetCode-129-求根節點到葉節點數字之和LeetCode
- zt_如何確定index root block split索引塊分裂的時間點IndexBloC索引
- 關於B*tree索引(index)的中度理解及bitmap 索引的一點探究(zt)索引Index
- java 關於操作Collection的一點說明Java
- 關於Inlist iterator操作的一點認識
- JavaScript學習之DOM（節點、節點層級、節點操作）JavaScript
- 關於SSL證書雙向認證該怎麼操作
- js原生節點操作JS
- Zookeeper--節點操作
- Oracle索引塊分裂split資訊彙總Oracle索引
- 資料庫索引分裂 問題分析資料庫索引
- 關於Laravel的表單驗證分層設計以及驗證場景的應用Laravel
- 索引的知識要點與操作索引
- 關於效率的一些思考：節點創新
- 關於索引的使用模式索引模式
- 關於軟體驗證中的單元測試
- 關於MySQL極限值的初步驗證糾錯（二）MySql
- 關於表的資料塊驗證 DUMP 工具 使用
- 關於資料庫管理員的驗證小結資料庫