9i index bug.txt 之2
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分裂。
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?OracleIndex
- 9i新特性之Flashback Query的應用(2)
- （轉）Index Rebuild Online 過程(9i)完整版IndexRebuild
- oracle hint之full,index,index_asc,index_desc,index_combile示例OracleIndex
- oracle hint之hint_index_ffs,index_joinOracleIndex
- 【江楓 】Oracle 9i和10g在create index和rebuild index的統計資訊的區別OracleIndexRebuild
- Oracle的Index－2（轉）OracleIndex
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- Oracle筆記 之 索引(index)Oracle筆記索引Index
- 10g在create index時就有索引統計資訊，9i 沒有Index索引
- 9I/10G 11G online index的實現過程分析Index
- Oracle 9i R2 配置 Logical StandbyOracle
- 關於Oracle 9i 跳躍式索引掃描(Index Skip Scan)的小測試 (轉)Oracle索引Index
- Parameter index out of range (3 > number of parameters, which is 2).Index
- ORACLE 9i歸檔之ARCn: Media recovery disabledOracle
- Oracle 9i 分析函式參考手冊(2)Oracle函式
- 【MySQL】效能優化之 index merge (1)MySql優化Index
- Oracle效能優化之“少做事”（rebuild index）Oracle優化RebuildIndex
- KEEP INDEX | DROP INDEXIndex
- [20191002]函式dump的bug.txt函式
- [20131221]12c 優化 bug.txt優化
- 9i新特性之Flashback Query的應用(1)
- 9i新特性之資料庫監控系列資料庫
- Vue-Router原始碼分析之index.jsVue原始碼IndexJS
- Solr小技巧之快速刪除index的方法SolrIndex
- MySQL5.6之use_index_extensions優化MySqlIndex優化
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- 317TABLE ACCESS BY INDEX ROWID BATCHED2IndexBAT
- [20171220]toad plsql顯示整形的bug.txtSQL
- [20120730]11g下Oracle Index rebuild online之2.txtOracleIndexRebuild
- 解析Oracle 8i/9i的計劃穩定性(2)Oracle
- INDEX UNIQUE SCAN，INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Clustered Index Scan and Clustered Index SeekIndex
- IndexIndex
- MySQL 優化之 index_merge （索引合併）MySql優化Index索引
- 【oracle 效能優化】組合索引之index_ssOracle優化索引Index
- oracle全文索引之About_INDEX_THEMES操作Oracle索引Index
- oracle全文索引之STOPLIST_ CTXCAT 索引_INDEX SETOracle索引Index