理解index leaf node 90-10 split
理解index leaf node 90-10 split
當索引leaf滿分裂時,存在兩種情況:
1.如果插入的鍵值是最大值,分裂按照90-10 split.
2.如果不是,按照50-50分裂。
按照字面的理解90-10 split,就是90的鍵值保留在原來資料塊中,其他10%鍵值使用新的資料塊。實際情況如何呢?
1.建立測試表:
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t1(a varchar2(5),b varchar2(10));
create unique index i_t1_a on t1(a);
select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%leaf%';
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
11 382 0 leaf node splits
11 383 0 leaf node 90-10 splits
begin
for i in 1..501 loop
insert into t1 values (lpad(to_char(i),5,'0') ,'test');
end loop;
end;
/
commit;
select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%leaf%';
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
11 382 1 leaf node splits
11 383 1 leaf node 90-10 splits
應該產生一次leaf node 90-10 splits。
2. 分析索引結構:
SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T1_A';
OBJECT_ID
----------
76265
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 76265';
Session altered.
----- begin tree dump
branch: 0x100051b 16778523 (0: nrow: 2, level: 1)
leaf: 0x100051e 16778526 (-1: nrow: 499 rrow: 499)
leaf: 0x100051f 16778527 (0: nrow: 2 rrow: 2)
----- end tree dump
可以發現兩個索引leaf node,1個有499個鍵值,另外一個2個鍵值。
3.轉儲資料塊:
select header_file, header_block from dba_segments where segment_name='I_T1_A';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 1306
根節點塊1307,根據上面leaf節點應該是1310,1311.
alter system dump datafile 4 block min 1310 block max 1311;
Start dump data blocks tsn: 4 file#:4 minblk 1310 maxblk 1311
........
Block header dump: 0x0100051f
Object id on Block? Y
seg/obj: 0x129e9 csc: 0x00.37946f itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000518 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.010.00000e0d 0x00c00a13.0222.02 CB-- 0 scn 0x0000.0037946f
0x02 0x0005.000.00000e0b 0x00c00a0f.0222.1c --U- 2 fsc 0x0000.00379471
Leaf block dump
===============
header address 46978354870884=0x2aba00277a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 1
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8004=0x1f44
kdxcoavs 7964
kdxlespl 0
kdxlende 0
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 16778526=0x100051e
kdxledsz 6
kdxlebksz 8032
row#0[8018] flag: ------, lock: 2, len=14, data:(6): 01 00 05 0e 00 2e
col 0; len 5; (5): 30 30 35 30 30
row#1[8004] flag: ------, lock: 2, len=14, data:(6): 01 00 05 0e 00 2f
col 0; len 5; (5): 30 30 35 30 31
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1310 maxblk 1311
也可以確定1311塊中僅僅兩個鍵值。
SQL> select dump('00500',16),dump('00501',16) from dual;
DUMP('00500',16) DUMP('00501',16)
---------------------------- ----------------------------
Typ=96 Len=5: 30,30,35,30,30 Typ=96 Len=5: 30,30,35,30,31
根據測試如果index leaf node 90-10 split時,實際上是新新增的鍵值使用新的索引extent,保留原來節點資訊。實際上應該叫100-1個鍵值的split。
當索引leaf滿分裂時,存在兩種情況:
1.如果插入的鍵值是最大值,分裂按照90-10 split.
2.如果不是,按照50-50分裂。
按照字面的理解90-10 split,就是90的鍵值保留在原來資料塊中,其他10%鍵值使用新的資料塊。實際情況如何呢?
1.建立測試表:
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t1(a varchar2(5),b varchar2(10));
create unique index i_t1_a on t1(a);
select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%leaf%';
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
11 382 0 leaf node splits
11 383 0 leaf node 90-10 splits
begin
for i in 1..501 loop
insert into t1 values (lpad(to_char(i),5,'0') ,'test');
end loop;
end;
/
commit;
select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%leaf%';
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
11 382 1 leaf node splits
11 383 1 leaf node 90-10 splits
應該產生一次leaf node 90-10 splits。
2. 分析索引結構:
SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T1_A';
OBJECT_ID
----------
76265
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 76265';
Session altered.
----- begin tree dump
branch: 0x100051b 16778523 (0: nrow: 2, level: 1)
leaf: 0x100051e 16778526 (-1: nrow: 499 rrow: 499)
leaf: 0x100051f 16778527 (0: nrow: 2 rrow: 2)
----- end tree dump
可以發現兩個索引leaf node,1個有499個鍵值,另外一個2個鍵值。
3.轉儲資料塊:
select header_file, header_block from dba_segments where segment_name='I_T1_A';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 1306
根節點塊1307,根據上面leaf節點應該是1310,1311.
alter system dump datafile 4 block min 1310 block max 1311;
Start dump data blocks tsn: 4 file#:4 minblk 1310 maxblk 1311
........
Block header dump: 0x0100051f
Object id on Block? Y
seg/obj: 0x129e9 csc: 0x00.37946f itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000518 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.010.00000e0d 0x00c00a13.0222.02 CB-- 0 scn 0x0000.0037946f
0x02 0x0005.000.00000e0b 0x00c00a0f.0222.1c --U- 2 fsc 0x0000.00379471
Leaf block dump
===============
header address 46978354870884=0x2aba00277a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 1
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8004=0x1f44
kdxcoavs 7964
kdxlespl 0
kdxlende 0
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 16778526=0x100051e
kdxledsz 6
kdxlebksz 8032
row#0[8018] flag: ------, lock: 2, len=14, data:(6): 01 00 05 0e 00 2e
col 0; len 5; (5): 30 30 35 30 30
row#1[8004] flag: ------, lock: 2, len=14, data:(6): 01 00 05 0e 00 2f
col 0; len 5; (5): 30 30 35 30 31
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1310 maxblk 1311
也可以確定1311塊中僅僅兩個鍵值。
SQL> select dump('00500',16),dump('00501',16) from dual;
DUMP('00500',16) DUMP('00501',16)
---------------------------- ----------------------------
Typ=96 Len=5: 30,30,35,30,30 Typ=96 Len=5: 30,30,35,30,31
根據測試如果index leaf node 90-10 split時,實際上是新新增的鍵值使用新的索引extent,保留原來節點資訊。實際上應該叫100-1個鍵值的split。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-713266/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [201112114]index leaf node 50-50 splitIndex
- 唯一index和非唯一index中leaf node裡rowid的一點區別。Index
- index branch and leaf block structure(轉)IndexBloCStruct
- index desc之後資料在leaf node中是如何儲存的!Index
- Oracle索引分裂(Index Block Split)Oracle索引IndexBloC
- Oracle index索引塊分裂split資訊彙總OracleIndex索引
- index block split相關一些知識IndexBloC
- Leaf
- index leaf block索引葉子塊何時會刪除—part1IndexBloC索引
- 理解index skip scanIndex
- mysql中substring_index類似split分組功能MySqlIndex
- mysql函式substring_index實現split切割效果MySql函式Index
- 【Oracle 12c Flex Cluster專題 】— Leaf Node的故障遷移OracleFlex
- [20220124]index split level.txtIndex
- 理解 Node.jsNode.js
- Node JS Buffer使用理解JS
- zt_如何確定index root block split索引塊分裂的時間點IndexBloC索引
- node.js簡單理解Node.js
- 在對比中理解 Node
- 理解 Node.js(譯文)Node.js
- INDEX JAVA 各類名詞理解 & 知識點理解 目錄IndexJava
- 說說你對z-index的理解Index
- Leaf for Mac RSS閱讀器Mac
- node之tcp篇入門理解TCP
- 理解SAP BW 中的 Bit-Map Index (續)Index
- JavaScript split()JavaScript
- Awk split
- split 例子
- split partition
- Edge Split
- Leetcode Sum Root to Leaf NumbersLeetCode
- 理解 Node.js 的事件輪詢Node.js事件
- 關於node.js中流的理解Node.js
- 【譯】理解Node事件驅動架構事件架構
- [Day2] Node.js利用Express訪問index.htmlNode.jsExpressIndexHTML
- zt_深入理解bitmap index點陣圖索引Index索引
- Leaf-分散式ID生成系統分散式
- Sum Root to Leaf Numbers leetcode javaLeetCodeJava