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分裂。