When to rebuild indexes

[blakmk]

When is a good time to rebuild indexes?

Are there any particular stats, queries that can be run to tell when an index is fragmented?

[JMac]

Analyze the indexes using

ANALYZE INDEX index_name VALIDATE STRUCTURE;

Then query the INDEX_STATS table and compare LF_ROWS with DEL_LF_ROWS.
if you have > 15% of the rows as deleted, then I'd rebuild the index.

[mrchrispy]

as INDEX_STATS only show the details for the last validate structure issued you'll be wanting to script/PLSQL something to look at all your indexes. It's been done a thousand times before so there are plenty freely available. I'm sure this can be done 'online' but I'm scared to try it can someone confirm this for me?

[slimdave]

If you are going to rebuild indexes then benchmark your performance before and afterwards. Probably you will find that there is no difference, but you might find that performance degrades. You might then reconsider whether you want to bother.

[KenEwald]

SlimDave, I'll have to disagree with you.

We regularly re-index and get significant performance increases. More boost on indexes with frequent updates. Not to mention the space recovered from unused leaf blocks.

Certainly worth it.

[slimdave]

Hey, I'm not saying it can't happen, just that it's unlikely.

Benchmarking is the key -- if it's faster afterwards then keep on going, but don't go thinking that index rebuilds are a quick and easy step to happiness. If you're not benchmarking your improvement then you're wasting time -- rebuilding indexes every month turns into a little religious rite that must be performed to keep the database gods happy.

[Orca777]

Hi,
If your index contains a columns with a growing ID and older rows are deleted due to the apllication-design, then you can rebuild the index regularly because your index growth in space and your leaf-blocks are not filled enough for effective read.

ORca


Be aware of that index-rebuild can cause the optimizer to choose another execution-plan, after collecting stats. This is very problematic with partitioned indexes.

[slimdave]

That sounds like it might be a candidate for a reverse index?

[tamilselvan]

Instead of REBUILDING, consider COALESCE.
The command is :
ALTER INDEX indexname COALESCE;

By coalescing, Oracle merges near emplty blocks and frees them for future insertion. Moreover, there won't be lock on the table, so the availability of the table is ensured. In 24x7 env, this is a most command I have come across.

Tamil