[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              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<=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 splits

SCOTT@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         105145

SCOTT@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 dump

SCOTT@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]
....

--//可以发现分裂后是索引键值是排序的.