本文共 6592 字，大约阅读时间需要 21 分钟。
[20160711]索引键值在B tree索引块中的顺序2.txt
--上午测试索引键值在B tree索引块中的顺序,许多人认为是有序,主要是插入后再建立索引.
--这样看到索引块里面的键值就是有序的.--今天测试一下,如果索引分裂后是否会排序呢?索引分裂有两种情况,先测试leaf node 50-50 splits的情况.
测试看看.1.环境:
SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0create table t (x varchar2(10));
insert into t select lpad(rownum,6,'0') from dual connect by level<=500; commit ; create index i_t_x on t(x) pctfree 0;SCOTT@test01p> select header_file,header_block from dba_segments where owner='SCOTT' and segment_name='I_T_X';
HEADER_FILE HEADER_BLOCK ----------- ------------ 9 178 --//dba=9,179 就是索引的root节点. SCOTT@test01p> select OWNER ,OBJECT_NAME, OBJECT_ID,DATA_OBJECT_ID from dba_objects where owner=user and object_name='I_T_X'; OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID ------ -------------------- ---------- -------------- SCOTT I_T_X 105144 105144SCOTT@test01p> alter session set events 'immediate trace name treedump level 105144';
Session altered.----- begin tree dump
branch: 0x24000b3 37748915 (0: nrow: 2, level: 1) leaf: 0x24000b4 37748916 (-1: nrow: 444 rrow: 444) leaf: 0x24000b5 37748917 (0: nrow: 56 rrow: 56) ----- end tree dump--很明显一个数据块能插入444条键值.重新插入看看.
SCOTT@test01p> truncate table t; Table truncated.SCOTT@test01p> insert into t select * from (select lpad(rownum,6,'0') a from dual connect by level<=445 order by DBMS_RANDOM.RANDOM) where a<>'000042';
444 rows created.SCOTT@test01p> commit ;
Commit complete.2.检查转储内容:
SCOTT@test01p> alter system checkpoint ; System altered.SCOTT@test01p> alter system dump datafile 9 block 179;
System altered.--检查看转储内容,明显并不是排序的.
.... 026136C00 00000000 00000000 30060200 30303030 [...........00000] 026136C10 40020632 98018E00 30060200 30303030 [2..@.......00000] 026136C20 40020633 4D018E00 30060200 30303030 [3..@...M...00000] 026136C30 40020635 0E018E00 30060200 30303030 [5..@.......00000] 026136C40 40020636 7F018E00 30060200 30303030 [6..@.......00000] 026136C50 40020639 4C018E00 30060200 31303030 [9..@...L...00001] 026136C60 40020630 A2018E00 30060200 31303030 [0..@.......00001] 026136C70 40020636 A5018E00 30060200 31303030 [6..@.......00001] 026136C80 40020638 7C018E00 30060200 31303030 [8..@...|...00001] 026136C90 40020639 1C018E00 30060200 32303030 [9..@.......00002] ...3.插入数据看看:
SCOTT@test01p> select * from t where x='000042';
no rows selected--//注意插入'000042',不是最大值,这个时候索引分裂是50-50.
SCOTT@test01p> select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like 'leaf%'; SID STATISTIC# VALUE CON_ID NAME ---------- ---------- ---------- ---------- ---------------------------------------- 130 572 0 0 leaf node splits 130 574 0 0 leaf node 90-10 splitsSCOTT@test01p> insert into t values('000042');
1 row created.SCOTT@test01p> commit ;
Commit complete.SCOTT@test01p> select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like 'leaf%';
SID STATISTIC# VALUE CON_ID NAME ---------- ---------- ---------- ---------- ---------------------------------------- 130 572 1 0 leaf node splits 130 574 0 0 leaf node 90-10 splits--可以发现发生了索引分裂,并且leaf node splits增加1,而leaf node 90-10 splits=0.说明50-50分裂.
SCOTT@test01p> alter system checkpoint ;
System altered.--这个时候root节点不是叶子节点.看看.
SCOTT@test01p> select OWNER ,OBJECT_NAME, OBJECT_ID,DATA_OBJECT_ID from dba_objects where owner=user and object_name='I_T_X'; OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID ------ -------------------- ---------- -------------- SCOTT I_T_X 105144 105145SCOTT@test01p> alter session set events 'immediate trace name treedump level 105144'; Session altered.
*** 2016-07-11 22:05:01.037
branch: 0x24000b3 37748915 (0: nrow: 2, level: 1) leaf: 0x24000b5 37748917 (-1: nrow: 219 rrow: 219) leaf: 0x24000b6 37748918 (0: nrow: 226 rrow: 226) ----- end tree dumpSCOTT@test01p> set verify off SCOTT@test01p> @ dfb16 0x24000b6 RFILE# BLOCK# TEXT ---------- ---------- ------------------------------------------------------------ 9 182 alter system dump datafile 9 block 182 ;
SCOTT@test01p> @ dfb16 0x24000b5
RFILE# BLOCK# TEXT ---------- ---------- ------------------------------------------------------------ 9 181 alter system dump datafile 9 block 181 ;--//分别转储看看.
alter system dump datafile 9 block 181 ;...
026137A00 00000000 00000000 30060200 34303030 [...........00004] 026137A10 40020632 BC018E00 30060202 30303030 [2..@.......00000] 026137A20 40020631 FC008E00 30060202 30303030 [1..@.......00000] 026137A30 40020632 98018E00 30060202 30303030 [2..@.......00000] 026137A40 40020633 4D018E00 30060202 30303030 [3..@...M...00000] 026137A50 40020634 B2008E00 30060202 30303030 [4..@.......00000] 026137A60 40020635 0E018E00 30060202 30303030 [5..@.......00000] 026137A70 40020636 7F018E00 30060202 30303030 [6..@.......00000] 026137A80 40020637 3C008E00 30060202 30303030 [7..@...<...00000] 026137A90 40020638 87008E00 30060202 30303030 [8..@.......00000] 026137AA0 40020639 4C018E00 30060202 31303030 [9..@...L...00001] 026137AB0 40020630 A2018E00 30060202 31303030 [0..@.......00001] 026137AC0 40020631 38008E00 30060202 31303030 [1..@...8...00001] ... --//如果仔细看可以发现是先分裂,而且分裂以后索引键值在块中是排序的,这也许是许多人看索引块键值是排序的原因. --//实际再仔细上看'000042'索引插入的也在最上面,索引键值也是从块底部开始插入的.并没有排序.alter system dump datafile 9 block 182 ;
...
0261379A0 00000000 00000000 30060202 32323030 [...........00022] 0261379B0 40020630 ED008E00 30060202 32323030 [0..@.......00022] 0261379C0 40020631 D6008E00 30060202 32323030 [1..@.......00022] 0261379D0 40020632 7A018E00 30060202 32323030 [2..@...z...00022] 0261379E0 40020633 4B018E00 30060202 32323030 [3..@...K...00022] 0261379F0 40020634 19008E00 30060202 32323030 [4..@.......00022] 026137A00 40020635 85008E00 30060202 32323030 [5..@.......00022] 026137A10 40020636 68008E00 30060202 32323030 [6..@...h...00022] 026137A20 40020637 8A018E00 30060202 32323030 [7..@.......00022] 026137A30 40020638 AC018E00 30060202 32323030 [8..@.......00022] 026137A40 40020639 26008E00 30060202 33323030 [9..@...&...00023] 026137A50 40020630 76018E00 30060202 33323030 [0..@...v...00023] 026137A60 40020631 B1018E00 30060202 33323030 [1..@.......00023] 026137A70 40020632 39018E00 30060202 33323030 [2..@...9...00023] 026137A80 40020633 A9008E00 30060202 33323030 [3..@.......00023] 026137A90 40020634 6C018E00 30060202 33323030 [4..@...l...00023] 026137AA0 40020635 94018E00 30060202 33323030 [5..@.......00023] ....--//可以发现分裂后是索引键值是排序的.
转载地址：http://vulxx.baihongyu.com/