Yaping's Weblog

August 30, 2008

Index split

Filed under: Oracle — Yaping @ 3:00 am
Tags:

There are two methods to handle index split, called 50/50 and 90/10 split. When index’s value changes monotonously, such as sequence, block split will use 90/10 split, it will decrease time and save space. But there is bug on 90/10 split.

 

Version 9204

 

Session 1

SQL> drop table t1;

Table dropped.

 

SQL> create table t1 (id number);

Table created.

 

SQL> create index idx_t1_id on t1(id);

Index created.

 

Session 2

SQL> l

  1* select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’)

SQL> /

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                        179

leaf node 90-10 splits                                                   36

branch node splits                                                        0

 

 

Session 1

SQL> begin

  2    for i in 1..10000 loop

  3        insert into t1 values (round(dbms_random.value(0,100000)));

  4    end loop;

  5    commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> analyze index idx_t1_id validate structure;

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE

———- ———- ———- ———- ———– ———- ———-

         2         40         32       7996           0         61     159251

 

 

Session 2

SQL> l

  1* select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’)

SQL> /

 

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                        210

leaf node 90-10 splits                                                   36

branch node splits                                                        0

 

 

 

session 1

SQL> truncate table t1;

Table truncated.

 

SQL> begin

  2    for i in 1..10000 loop

  3        insert into t1 values (i);

  4    end loop;

  5    commit;        

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> analyze index idx_t1_id validate structure;

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE

———- ———- ———- ———- ———– ———- ———-

         2         24         19       7996           0         94     149999

 

 

Session 2

SQL> l

  1* select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’)

SQL> /

 

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                        228

leaf node 90-10 splits                                                   54

branch node splits                                                        0

 

 

session 1

SQL> truncate table t1; 

Table truncated.

 

SQL> begin

  2    for i in 1..10000 loop

  3        insert into t1 values (i);

  4        commit;

  5    end loop;       

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> analyze index idx_t1_id validate structure;

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE

———- ———- ———- ———- ———– ———- ———-

         2         40         36       7996           0         51     150184

 

 

Session 2

SQL> l

  1* select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’)

SQL> /

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                        263

leaf node 90-10 splits                                                   54

branch node splits                                                        0

 

 

Oops, the last case split leaf blocks by 50/50 method, but the key value increases monotonously, the less half will never reinsert data again. Some systems have primary key based on sequence on every tables, if in this version, a mass of space are wasted.

 

Behaving of index split in 9208 is similar.

 

 

Version 10203

 

Session 2

SQL> create tablespace test datafile ‘/oradata/ora10g/test/test01.dbf’ size 20m segment space management manual;

Tablespace created.

 

SQL> select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’);

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                          0

leaf node 90-10 splits                                                    0

branch node splits                                                        0

 

 

session 1

SQL> drop table t1;

Table dropped.

 

SQL> create table t1 (id number) tablespace test;

Table created.

 

SQL> create index idx_t1_id on t1(id) tablespace test;

Index created.

 

SQL> begin

  2    for i in 1..10000 loop

  3        insert into t1 values (i);

  4        commit;

  5    end loop;      

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> analyze index idx_t1_id validate structure;

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE

———- ———- ———- ———- ———– ———- ———-

         2         24         19       7996           0         94     149999

 

 

 

Session 2

SQL> l

  1* select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’)

SQL> /

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                         18

leaf node 90-10 splits                                                   18

branch node splits                                                        0

 

 

 

session 1

SQL> truncate table t1;

Table truncated.

 

SQL> begin

  2    for i in 1..10000 loop

  3        insert into t1 values (i);

  4    end loop;

  5    commit;      

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> analyze index idx_t1_id validate structure;

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,LF_BLKS,LF_BLK_LEN,DEL_LF_ROWS,PCT_USED,USED_SPACE from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_BLK_LEN DEL_LF_ROWS   PCT_USED USED_SPACE

———- ———- ———- ———- ———– ———- ———-

         2         24         19       7996           0         94     149999

 

 

Session 2

SQL> l

  1* select name,value from v$sysstat where name in (‘leaf node splits’,’leaf node 90-10 splits’,’branch node splits’)

SQL> /

NAME                                                                  VALUE

—————————————————————- ———-

leaf node splits                                                         36

leaf node 90-10 splits                                                   36

branch node splits                                                        0

 

It seems this issue is fixed in this version.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: