Rebuilding Indexes for better Performance

Hi All, wishing you a Very Happy New Year and great success and learning this year. I am continuing from my last post http://performancetestingcentre.blogspot.in/2014/10/analyzing-extent-of-index-fragmentation.html

After you analyse the index report, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries.

You can use the following command

ALTER INDEX <<index_name>> REBUILD;

You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. You can use the below command to do so.

ALTER INDEX <<index_name>> REBUILD ONLINE;

Rebuilding an index based on an existing data source removes intra-block fragmentation. If we compare this to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

Before considering the rebuilding of indexes you should also check the below conditions for Rebuilds.

• Large free space (generally 50%+), which indexes rarely reach, and
• Large selectivity, which most index accesses never reach, and
• Response times are adversely affected, which rarely are.
• Note requirement of some free space anyways to avoid insert and
• subsequent free space issues

Click here to know more


Benefits of rebuild based on various dependencies which include:

• Size of index
• Clustering Factor
• Caching characteristics
• Frequency of index accesses
• Selectivity (cardinality) of index accesses
• Range of selectivity (random or specific range)
• Efficiency of dependent SQL
• Fragmentation characteristics (does it effect portion of index frequently used)
• I/O characteristics of index (serve contention or I/O bottlenecks)

Myths:

• The vast majority of indexes do not require rebuilding
• Oracle B-tree indexes can become "unbalanced" and need to be rebuilt is a myth
• Deleted space in an index is "deadwood" and over time requires the index to be rebuilt is a myth
• If an index reaches "x" number of levels, it becomes inefficient and requires the index to be rebuilt is a myth
• If an index has a poor clustering factor, the index needs to be rebuilt is a myth
• To improve performance, indexes need to be regularly rebuilt is a myth

Sometimes you create Indexes that are not even used and you can drop those Indexes, I will explain more on that in my next post. Happy Learning :)