ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 9i index bug.txt 之2

9i index bug.txt 之2

原创 Linux操作系统 作者:lfree 时间:2011-12-12 15:09:27 0 删除 编辑
9i index bug.txt 之2

1.接着以上的测试:

SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T1_A';
 OBJECT_ID
----------
     45851
 
        To then do a treedump of the index:
 
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 45851';
Session altered.

*** 2011-12-12 11:32:10.328
*** SESSION ID:(103.12307) 2011-12-12 11:32:10.312
----- begin tree dump
branch: 0x2000334 33555252 (0: nrow: 18, level: 1)
   leaf: 0x2000336 33555254 (-1: nrow: 578 rrow: 578)
   leaf: 0x2000337 33555255 (0: nrow: 571 rrow: 571)
   leaf: 0x2000338 33555256 (1: nrow: 571 rrow: 571)
   leaf: 0x2000335 33555253 (2: nrow: 571 rrow: 571)
   leaf: 0x2000346 33555270 (3: nrow: 571 rrow: 571)
   leaf: 0x2000347 33555271 (4: nrow: 571 rrow: 571)
   leaf: 0x2000348 33555272 (5: nrow: 571 rrow: 571)
   leaf: 0x2000341 33555265 (6: nrow: 571 rrow: 571)
   leaf: 0x2000342 33555266 (7: nrow: 571 rrow: 571)
...........
   leaf: 0x200035d 33555293 (15: nrow: 571 rrow: 571)
   leaf: 0x200035e 33555294 (16: nrow: 286 rrow: 286)
----- end tree dump

可以发现第1个子leaf占用578.

SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T2_A';
 OBJECT_ID
----------
     45849

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 45849';
Session altered.

*** SESSION ID:(47.16996) 2011-12-12 11:34:56.453
----- begin tree dump
branch: 0x200033c 33555260 (0: nrow: 34, level: 1)
   leaf: 0x200033e 33555262 (-1: nrow: 297 rrow: 297)
   leaf: 0x200033f 33555263 (0: nrow: 290 rrow: 290)
........
   leaf: 0x200052d 33555757 (30: nrow: 290 rrow: 290)
   leaf: 0x200052e 33555758 (31: nrow: 290 rrow: 290)
   leaf: 0x200052f 33555759 (32: nrow: 423 rrow: 423)
----- end tree dump


        从第1块索引记录从1-578.(也就是满的时候是占到578项).如果在这个情况下单独插入1条579情况如何。

1.开始建立测试:
create table t4 (a number,b varchar2(10));
create unique index i_t4_a on t4(a);

begin
        for i in 1..576   loop
                insert into t4 values (i,'test');
        end loop;
end;
/
commit;

SQL> 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
---------- ---------- ---------- ------------------------------
        47        201          0 leaf node splits
        47        202          0 leaf node 90-10 splits

可以发现分裂没有发生!

insert into t4 values (577,'test');
insert into t4 values (578,'test');

SQL> 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
---------- ---------- ---------- ------------------------------
        47        201          0 leaf node splits
        47        202          0 leaf node 90-10 splits

可以发现分裂依旧没有发生!        不提交执行:

insert into t4 values (579,'test');

SQL> 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
---------- ---------- ---------- ------------------------------
        47        201          1 leaf node splits
        47        202          1 leaf node 90-10 splits

可以发现分裂发生,并且执行的是90-10 splits.

2.删除表再重复测试,这次每次都commit。

drop table t4;
create table t4 (a number,b varchar2(10));
create unique index i_t4_a on t4(a);

begin
        for i in 1..576   loop
                insert into t4 values (i,'test');
        end loop;
end;
/
commit;

SQL> 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
---------- ---------- ---------- ------------------------------
        47        201          0 leaf node splits
        47        202          0 leaf node 90-10 splits

insert into t4 values (577,'test');
commit;
insert into t4 values (578,'test');
commit;

SQL> 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
---------- ---------- ---------- ------------------------------
        47        201          0 leaf node splits
        47        202          0 leaf node 90-10 splits

可以发现分裂没有发生!

insert into t4 values (579,'test');

SQL> 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
---------- ---------- ---------- ------------------------------
        47        201          1 leaf node splits
        47        202          0 leaf node 90-10 splits
可以发现分裂没有发生!分裂是50-50的分裂。

SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T4_A';

 OBJECT_ID
----------
     45864

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 45864';

*** 2011-12-12 11:58:52.593
*** SESSION ID:(47.16998) 2011-12-12 11:58:52.578
----- begin tree dump
branch: 0x2000ec4 33558212 (0: nrow: 2, level: 1)
   leaf: 0x2000ec6 33558214 (-1: nrow: 297 rrow: 297)
   leaf: 0x2000ec7 33558215 (0: nrow: 282 rrow: 282)
----- end tree dump

可以猜测(仅仅猜测),如果在索引块满的情况下,再插入一个递增的键值,9i下索引的分裂是安装50-50分裂。

3.从上面再作出一个假设:
索引块满基本都是占用571个条目。前面一个占用578,我使用的索引键值是数字,占用长度存在一定的变化,不好估算。
SQL> select a,dump(a) x  from t1 where dump(a) like '%Len=2:%' order by a ;
         A X
---------- --------------------
         1 Typ=2 Len=2: 193,2
         2 Typ=2 Len=2: 193,3
         3 Typ=2 Len=2: 193,4
         4 Typ=2 Len=2: 193,5
         5 Typ=2 Len=2: 193,6
         6 Typ=2 Len=2: 193,7
         7 Typ=2 Len=2: 193,8
         8 Typ=2 Len=2: 193,9
         9 Typ=2 Len=2: 193,10
        10 Typ=2 Len=2: 193,11
        11 Typ=2 Len=2: 193,12
                .....
      8100 Typ=2 Len=2: 194,82
      8200 Typ=2 Len=2: 194,83
      8300 Typ=2 Len=2: 194,84
      8400 Typ=2 Len=2: 194,85
      8500 Typ=2 Len=2: 194,86
      8600 Typ=2 Len=2: 194,87
      8700 Typ=2 Len=2: 194,88
      8800 Typ=2 Len=2: 194,89
      8900 Typ=2 Len=2: 194,90
      9000 Typ=2 Len=2: 194,91
      9100 Typ=2 Len=2: 194,92
      9200 Typ=2 Len=2: 194,93
      9300 Typ=2 Len=2: 194,94
      9400 Typ=2 Len=2: 194,95
      9500 Typ=2 Len=2: 194,96
      9600 Typ=2 Len=2: 194,97
      9700 Typ=2 Len=2: 194,98
      9800 Typ=2 Len=2: 194,99
      9900 Typ=2 Len=2: 194,100
     10000 Typ=2 Len=2: 195,2

199 rows selected.


4.建立一个表t5,保持索引建立长度一致,并且插入是线性增加的。
create table t5(a varchar2(5),b varchar2(10));
create unique index i_t5_a on t5(a);

begin
        for i in 1..1000   loop
                insert into t5 values (lpad(to_char(i),5,'0') ,'test');
        end loop;
end;
/
commit;

SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T5_A';
 OBJECT_ID
----------
     45866
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 45866';
Session altered.

----- begin tree dump
branch: 0x2000ed4 33558228 (0: nrow: 2, level: 1)
   leaf: 0x2000ed5 33558229 (-1: nrow: 500 rrow: 500)
   leaf: 0x2000ed6 33558230 (0: nrow: 500 rrow: 500)
----- end tree dump


drop table t5;
create table t5(a varchar2(5),b varchar2(10));
create unique index i_t5_a on t5(a);

begin
        for i in 1..1000   loop
                insert into t5 values (lpad(to_char(i),5,'0') ,'test');
                                commit ;
        end loop;
end;
/
commit;

SQL> SELECT object_id FROM dba_objects WHERE object_name = 'I_T5_A';
 OBJECT_ID
----------
     45868
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 45868';
Session altered.

----- begin tree dump
branch: 0x2000ed4 33558228 (0: nrow: 4, level: 1)
   leaf: 0x2000ed5 33558229 (-1: nrow: 249 rrow: 249)
   leaf: 0x2000ed6 33558230 (0: nrow: 250 rrow: 250)
   leaf: 0x2000ed8 33558232 (1: nrow: 250 rrow: 250)
   leaf: 0x2000ed7 33558231 (2: nrow: 251 rrow: 251)
----- end tree dump

5.可以发现索引满占用500项,如果分裂第1次占用249,以后分裂占用250.

$ factor 500
500: 2 2 5 5 5

begin
        for i in 1..1000         loop
                insert into t5 values (lpad(to_char(i),5,'0'),'test');
                                if (mod(i,&N) =0) then
                                          commit;
                                end if;
        end loop;
end;
/

如果N=2,5,10,20,25,50,100,250第1次分裂都会50-50.

做一个N=5的情况。
SQL> 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
---------- ---------- ---------- ------------------------------
        19        201          2 leaf node splits
        19        202          1 leaf node 90-10 splits

----- begin tree dump
branch: 0x2000ed4 33558228 (0: nrow: 3, level: 1)
   leaf: 0x2000ed5 33558229 (-1: nrow: 249 rrow: 249)
   leaf: 0x2000ed6 33558230 (0: nrow: 500 rrow: 500)
   leaf: 0x2000ed7 33558231 (1: nrow: 251 rrow: 251)
----- end tree dump

第1次插入到500时占好满,插入501正好分裂,按照50-50分裂。有251项在下一个索引块中,由于剩下仅仅放249条,不能被5整除。所以下次分别90-10分裂。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-713165/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 9i index bug?
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2349
  • 访问量
    6091548