[20160711]索引键值在B tree索引块中的顺序3.txt
--上午测试索引键值在B tree索引块中的顺序,许多人认为是有序,主要是插入后再建立索引.
--这样看到索引块里面的键值就是有序的.
--今天测试一下,如果索引分裂后是否会排序呢?索引分裂有两种情况,前面测试leaf node 50-50 splits的情况.
--继续测试leaf node 90-10 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 0
create 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 105144
SCOTT@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<=888 ) ;
888 rows created.
SCOTT@test01p> commit ;
Commit complete.
insert into t values('000889');
commit;
SCOTT@test01p> insert into t select * from (select lpad(rownum+889,6,'0') a from dual connect by level<=443 order by DBMS_RANDOM.RANDOM) ;
443 rows created.
SCOTT@test01p> commit ;
Commit complete.
2.检查转储内容:
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter session set events 'immediate trace name treedump level 105144';
Session altered.
----- begin tree dump
branch: 0x24000b3 37748915 (0: nrow: 3, level: 1)
leaf: 0x24000b5 37748917 (-1: nrow: 444 rrow: 444)
leaf: 0x24000b6 37748918 (0: nrow: 444 rrow: 444)
leaf: 0x24000b7 37748919 (1: nrow: 444 rrow: 444)
----- end tree dump
SCOTT@test01p> @ dfb16 0x24000b7
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
9 183 alter system dump datafile 9 block 183 ;
SCOTT@test01p> alter system dump datafile 9 block 183 ;
System altered.
...
026136C00 00000000 00000000 30060200 39383030 [...........00089]
026136C10 40020633 0F008B00 30060200 39383030 [3..@.......00089]
026136C20 40020634 15008B00 30060200 39383030 [4..@.......00089]
026136C30 40020635 58008B00 30060200 39383030 [5..@...X...00089]
026136C40 40020636 59008B00 30060200 39383030 [6..@...Y...00089]
026136C50 40020637 01008B00 30060200 39383030 [7..@.......00089]
026136C60 40020638 05008B00 30060200 30393030 [8..@.......00090]
026136C70 40020630 57028F00 30060200 30393030 [0..@...W...00090]
026136C80 40020632 11008B00 30060200 30393030 [2..@.......00090]
026136C90 40020633 79008B00 30060200 30393030 [3..@...y...00090]
...
--明显没有排序.
3.插入数据看看:
SCOTT@test01p> select max(x) from t;
MAX(X)
----------
001332
--444*3+1=1333
SCOTT@test01p> select * from t where x='001333';
no rows selected
--//注意插入'001333',是最大值,这个时候索引分裂是90-10.
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 splits
SCOTT@test01p> insert into t values('001333');
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 1 0 leaf node 90-10 splits
--可以发现发生了索引分裂,并且leaf node splits增加1,而leaf node 90-10 splits=1.说明90-10分裂.
--这种分裂实际上原来的100:1条记录的分裂.并不是90-10分裂.
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter session set events 'immediate trace name treedump level 105144';
Session altered.
----- begin tree dump
branch: 0x24000b3 37748915 (0: nrow: 4, level: 1)
leaf: 0x24000b5 37748917 (-1: nrow: 444 rrow: 444)
leaf: 0x24000b6 37748918 (0: nrow: 444 rrow: 444)
leaf: 0x24000b7 37748919 (1: nrow: 444 rrow: 444)
leaf: 0x24000b4 37748916 (2: nrow: 1 rrow: 1)
----- end tree dump
SCOTT@test01p> set verify off
SCOTT@test01p> @ dfb16 0x24000b7
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
9 183 alter system dump datafile 9 block 183 ;
--//分别转储看看.
alter system dump datafile 9 block 183 ;
...
020C36400 00000000 00000000 30060000 38383030 [...........00088]
020C36410 40020639 1B018F00 30060000 39383030 [9..@.......00089]
020C36420 40020630 46018F00 30060000 39383030 [0..@...F...00089]
020C36430 40020631 3B018F00 30060000 39383030 [1..@...;...00089]
020C36440 40020632 B2018F00 30060000 39383030 [2..@.......00089]
020C36450 40020633 0F008B00 30060000 39383030 [3..@.......00089]
020C36460 40020634 15008B00 30060000 39383030 [4..@.......00089]
020C36470 40020635 58008B00 30060000 39383030 [5..@...X...00089]
020C36480 40020636 59008B00 30060000 39383030 [6..@...Y...00089]
020C36490 40020637 01008B00 30060000 39383030 [7..@.......00089]
020C364A0 40020638 05008B00 30060000 39383030 [8..@.......00089]
020C364B0 40020639 A3018F00 30060000 30393030 [9..@.......00090]
020C364C0 40020630 57028F00 30060000 30393030 [0..@...W...00090]
020C364D0 40020631 1D018F00 30060000 30393030 [1..@.......00090]
020C364E0 40020632 11008B00 30060000 30393030 [2..@.......00090]
020C364F0 40020633 79008B00 30060000 30393030 [3..@...y...00090]
020C36500 40020634 5A018F00 30060000 30393030 [4..@...Z...00090]
020C36510 40020635 04008B00 30060000 30393030 [5..@.......00090]
...
--//可以发现分裂后索引键值是排序的.也许是许多人看索引块键值是排序的原因.