[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]
...

--//可以发现分裂后索引键值是排序的.也许是许多人看索引块键值是排序的原因.