Oracle Index Splits: What happens when index leaf block is fulland a new key has to insert in the same leaf block

We all know that Oracle maintain index keys as a pointers to the original rows of table and there are different types of indexes (Btree and bitmap), while what if the index entries are getting inserted either last of index leaf blocks or in between of leaf block and those leaf blocks are already full. BTW, for btree indexes you might have already know that logical structure would be root, branch and leaf blocks.

In order to get the index keys inserted into leaf blocks that already full, oracle splits the leaf blocks in two ways.

90-10 Splits

1) Indexes that has monotonically increased values Eg: sequence based ID columns, date,etc, the following btree index has an entries of 1-35 and packed across 10 blocks and there is no room for new entry, if a new value 36 has to be insert

Oracle splits the block as below. This is called Index block 90-10 splits. Here the the 35 the old key (assuming approximate of 10% ) split to new block and insert of new key 36 inserted.

Index block 50-50 Split

2) Indexes that has pairs of values Ex: Names, Regions etc. Here names are sorted with alphabets for example, A,BC etc…. and inserted keys are in between the blocks (not at the end of right most leaf block)

Now If a user has inserted a row/key with name again a G , here in our case Index leaf block 2, if it is already full, then Oracle will create two index leaf blocks under the same branch splitting the original keys to 50-50 % and then discard the original leaf block. this is called 50-50 leaf Splits.

Typically the above is for B-tree indexes and may not exact representation of index structure, but can give some insight about splits

-Thanks

Geek DBA

References:-

http://richardfoote.wordpress.com/

http://hemantoracledba.blogspot.in/2012/05/index-block-splits.html