In his famous index internals presentation Richard Foote mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.
While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G.
Here is the test case Richard presents in his paper.
SQL> create table t(id number,value varchar2(10));
Table created.
SQL> create index t_ind on t(id);
Index created.
SQL> @mystat split
NAME VALUE
------------------------------ ----------
leaf node splits 0
leaf node 90-10 splits 0
branch node splits 0
SQL> ed
Wrote file afiedt.buf
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 commit;
5 end loop;
6* end;
SQL> r
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 commit;
5 end loop;
6* end;
PL/SQL procedure successfully completed.
SQL> @mystat2 split
NAME VALUE DIFF
------------------------------ ---------- ----------
leaf node splits 35 35
leaf node 90-10 splits 0 0
branch node splits 0 0
SQL> analyze index t_ind validate structure;
Index analyzed.
SQL> select lf_blks, pct_used from index_stats;
LF_BLKS PCT_USED
---------- ----------
36 51
SQL> drop table t;
Table dropped.
I am trying to insert the rows in the order of the primary key column, so what I expect to see is that when an index block fills there will be a 90-10 split and the index will grow in size. But as the number of leaf block splits show there are 35 block splits and none of them are 90-10 splits meaning all are 50-50 block splits. I have 36 leaf blocks but half of each one is empty.
If we try the same inserts but commit after the loop the result changes.
SQL> create table t(id number,value varchar2(10));
Table created.
SQL> create index t_ind on t(id);
Index created.
SQL> @mystat split
NAME VALUE
------------------------------ ----------
leaf node splits 35
leaf node 90-10 splits 0
branch node splits 0
SQL> ed
Wrote file afiedt.buf
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6* end;
SQL> r
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6* end;
PL/SQL procedure successfully completed.
SQL> @mystat2 split
NAME VALUE DIFF
------------------------------ ---------- ----------
leaf node splits 53 53
leaf node 90-10 splits 18 18
branch node splits 0 0
SQL> analyze index t_ind validate structure;
Index analyzed.
SQL> select lf_blks, pct_used from index_stats;
LF_BLKS PCT_USED
---------- ----------
19 94
In this case we see that there have been 18 block splits and all were 90-10 splits as expected. We have 19 leaf blocks and all are nearly full. Depending on where the commit is we can get an index twice the size it has to be. When I ran the same test in 10G it did not matter where the commit was. I got 19 leaf blocks in both cases.
I did not test if this problem happens when several sessions insert a single row and commit just like in an OLTP system but I think it is likely because we have indexes showing this behavior in OLTP systems.
