Index block split bug in 9i

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.

OTN members, don't change your e-mail account!

I am regular user of OTN and its forums. Last week I was trying to login to OTN from a public computer and I got the "invalid login" error everytime I tried. I was sure I was typing my password correct but I could not get in anyway. So, I tried to get my password reset and sent to my e-mail address. Then I remembered that the e-mail address I used to register for OTN was from my previous employer meaning I did not have access to it anymore. As OTN does not allow changing the registration e-mail address I was stuck. I send a request from OTN to get my password delivered to my current e-mail address. Here is the reply I got:

Resolution: Oracle's membership management system does not currently support
the editing of the email address or username in your membership profile.
(It will support this capability in a future release.)
Please create a new account with the new email address you wish to use. However,
it is possible to change the email at which you receive
Discussion Forum "watch" emails (see "Your Control Panel" when logged in).

They tell me to create a new user and forget about my history, watch list, everything. What a user centric approach this is.

If you are an OTN member do not lose your password and your e-mail account at the same time, you will not find anybody from OTN who is willing to solve your problem and help you to recover your password.

I am used to bad behavior and unwillingness to solve problems in Metalink, now I get the same behavior in OTN. Whatever, just wanted to let you know about it.