Thursday, October 6, 2016

Rebuilding Index in Oracle

Rebuilding Index in Oracle


One Q. before proceeding ahead, Do You really want to rebuild Index ? Why?

Ask Yourself, before rebuilding INDEX.
- Performance Issue ? :- Have you taken any matrices for the same? If not, first identity which index and how much performance gain you will get after rebuilding index.
- Reclaim Space? :- How long it will take? How much space will be reclaimed? Don't it effects on your current stats of your indexes?

Key points need to consider before rebuilding INDEX.
- Rebuilding requires 2x space.
- You will have the old and the new index for a period of time.
- If it is online, It will need additional space to hold the changes that are made during the rebuild as well.


Tom Kyte views on rebuilding INDEX. (Must Read Article)
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

I like below two quotes from Tom Kyte's article
- It is the RARE index that needs to be rebuilt.
- It is not a rule that indexes need to be rebuilt.

When actually INDEX rebuild requires?
- High number of deleted leaf nodes
- Index height goes bigger
- Gets per indexes access is higher (Logical I/O)

Mostly above cases are happen in Datawarehouse environment, where data loading/unloading happens frequently.
In OLTP environment, such high data deletion will occur very rarely, hence rebuilding will not require at all.

Some Myths about INDEX rebuild:

- Oracle B-tree indexes can become "unbalanced", rebuild require to balance the b-tree index.
- Deleted space in an index is "deadwood" hence rebuild is require.
- poor clustering factor of index require rebuild.
- To improve performance, indexes need to be rebuild regularly

Now Truth about INDEXes
- Balancing done automatically. It doesn't require rebuilding.
- Deleted space will be reused.
- Rebuild will change position of rows hence blocks will be reloaded in buffer cache. Performance improvement is not immediate.

What exactly you need for good INDEXes (recommendation...)?
- Use Locally Managed tablespace for INDEXes.
Yes, only one recommendation. Oracle will automatically take care rest of the things (i.e. balancing & space utilization).


Hope my this article has clear your doubts about INDEXes...



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert