Regarding INDEX_STATS

[dbafreak]

After I analyze the indexes, I understand the statistics are populated into the INDEX_STATS table.

Can someone please explain me what these columns in INDEX_STATS are:

1)Height
2)del_lf_rows_len
3)lf_rows_len
4)del_lf_rows
5)lf_rows

Kindly explain what each of these columns mean?

[marist89]

http://otn.oracle.com/docs/products/...2292.htm#10867

[dbafreak]

What does "Height" mean here? Can someone please explain what it means when it says Height > 4 is a good candidate for rebuilding indexes. Pls advise.

[tatireddi]

I thnk it is similar to BLEVEL column in the user_indexes.
which explains how the B-Tree is distributed. By which we will decide to rebuild the index or not.

[jmodic]

"Height" of the B*tree index tells you how "levels" your index structure have. For example height of 4 means you have four levels in your index:

1st level - root block
2nd level - first level of your branch blocks
3rd level - second level of your branch blocks
4th level - your leaf blocks (wher your actual index entries are stored)

So whenever you have to locate an index entry from your index, you have to traverse 4 levels (read 4 index blocks) to get to your desired entry.

[dbafreak]

Originally posted by jmodic
"Height" of the B*tree index tells you how "levels" your index structure have. For example height of 4 means you have four levels in your index:

1st level - root block
2nd level - first level of your branch blocks
3rd level - second level of your branch blocks
4th level - your leaf blocks (wher your actual index entries are stored)

So whenever you have to locate an index entry from your index, you have to traverse 4 levels (read 4 index blocks) to get to your desired entry.

Thanks Jurij. I am sure I am missing something very trivial here. Now what decides the number of levels? I mean under what circumstances does the levels increase? I am trying to understand this as we need to rebuild the indexes if the height is > 4.

Appreciate your time and patience.

[dbafreak]

Jurij I am waiting for your reply :-)

[tamilselvan]

The number of branch levels will be increased due to more insert (one side) and more deletes or due to split block also.
Then Oracle has to read more blocks (one root, many branches) to arrive the required leaf level. That is why when height is > 4, it is advised to rebuild the index.

Also, after a large delete, always rebuild the indexes on big tables.

[jmodic]

When you create an index on an empty table, that index cosists of a single block. When you start inserting rows into table, index entries are inserted ito that index block as well. So in the beginning that block index plays a role of both leaf block and root block and the initial height of the index is 1.

By adding new and new rows, there comes a moment when there is no more room in that block to store additional index entry. At that time that block "splits" into two block. Approximately 50% of entries remain in their original block and other half entries are transfered into a new block. At the same time another new index block is formated, and that new index block serves as a new root block of the index. So now we have two leaf blocks that store actual index entries and one root block that stores ponters to the branch blocks. At that stage the height of the index is 2.

While new rows are inserted, the leaf blocks keep getting filled and they must be split on and on. An there must be a new pointer inserted into root block for every new leaf block in the root block. And then it will come a moment when there will be no more space in a root block for a new pointer to the newly created leaf block. At that moment Oracle has to rearange index structure. Old root block is split into two blocks that now serve as branch blocks. Each of the two new branch block holds 50% of the laf block pointers that were in a root block before the split. At the same time a new block is formated that serves as the new root block and it holds pointers to the two branch blocks. Now our index structure is like this:

- a layer of leaf block
- two branch blocks
- one root block

Our index height now is 3.

With new and new index entries new leaf blocks are getting created and branch blocks must be split as well when they get filled. So our index tree is growing, there are more and more branch blocks and root block has more and more pointers to the branch blocks. Once the root block again can not add another pointer for a newly created branch block, the whole process repeats as described above, adding one new layer of branch blocks. So now we have index that is 4 levels heigh:

- a layer of leaf blocks
- first layer of branch blocks with pointers to leaf blocks
- second layer of branch blocks with pointers to first layer branch blocks
- root block with pointers to second layer branch blocks

An the process continues that way - index grows horizontaly at first and then at some point it must grow also verticaly by one level...

[dbafreak]

Originally posted by jmodic
When you create an index on an empty table, that index cosists of a single block. When you start inserting rows into table, index entries are inserted ito that index block as well. So in the beginning that block index plays a role of both leaf block and root block and the initial height of the index is 1.

By adding new and new rows, there comes a moment when there is no more room in that block to store additional index entry. At that time that block "splits" into two block. Approximately 50% of entries remain in their original block and other half entries are transfered into a new block. At the same time another new index block is formated, and that new index block serves as a new root block of the index. So now we have two leaf blocks that store actual index entries and one root block that stores ponters to the branch blocks. At that stage the height of the index is 2.

While new rows are inserted, the leaf blocks keep getting filled and they must be split on and on. An there must be a new pointer inserted into root block for every new leaf block in the root block. And then it will come a moment when there will be no more space in a root block for a new pointer to the newly created leaf block. At that moment Oracle has to rearange index structure. Old root block is split into two blocks that now serve as branch blocks. Each of the two new branch block holds 50% of the laf block pointers that were in a root block before the split. At the same time a new block is formated that serves as the new root block and it holds pointers to the two branch blocks. Now our index structure is like this:

- a layer of leaf block
- two branch blocks
- one root block

Our index height now is 3.

With new and new index entries new leaf blocks are getting created and branch blocks must be split as well when they get filled. So our index tree is growing, there are more and more branch blocks and root block has more and more pointers to the branch blocks. Once the root block again can not add another pointer for a newly created branch block, the whole process repeats as described above, adding one new layer of branch blocks. So now we have index that is 4 levels heigh:

- a layer of leaf blocks
- first layer of branch blocks with pointers to leaf blocks
- second layer of branch blocks with pointers to first layer branch blocks
- root block with pointers to second layer branch blocks

An the process continues that way - index grows horizontaly at first and then at some point it must grow also verticaly by one level...

Superb!! Fascinating!! Excellent Jmodic. Thanks a ton for the crystal-clear explaination. Really appreciate your time and patience. Thanks once again.