As a followup to my earlier post "Rebuilding Indexes", here are some conditions and reasons when you would [or may need to] rebuild indexes.
1. You've done a very large purge of records on a table that is frequently undergoing FullTableScans. That table is a candidate for a Table Rebuild. An Index Rebuild, obviously follows. [do I hear cries of "foul" ? That this is not a reason for Index Rebuilds by themselves ?. Well I've never said that indexes, by themselves, have their own reasons to be rebuilt. If you see my earlier post, I've amply qualified rebuilding -- rebuilding follows from dependencies. A Table Rebuild is a good cause].
2. An index that suffers inserts of monotonically increasing values but does not see large purges of "left-most" data. Such behaviour would leave you with index blocks that are not reused. [This is different from the case where deletes do cause index blocks to be reused simply because *all* the key values in the candidate blocks are deleted, placing the blocks on the free list]. Let's say you have an index on a "ACTION_ID" or "ACTION_TIMESTAMP" of an Audit / Logging table. For such a table, you do not purge ALL records older than "N" days during your regular purge cycles. Some records which are indicators of key actions / critical updates [and suspicious actions] may be excluded from your purge. [If you did purge all records older than "N" days, the index blocks would be reusable.]
3. You have an index that is growing large and also has a very large key size and you also have a daily / frequent batch job inserting very many rows, each insert [being regular DML] having to "flood" the buffer cache with dirty index blocks. Logically, a DROP, INSERT, REBUILD would be preferred. However, this may be a PK or Unique Index and you do not wish to drop it. An Insert with an APPEND hint causing deferred index maintenance is not yet in place.
4. Experience with certain indexes has proven that Key Daily / Fortnightly Reports perform better, within very tight SLAs, after Index Rebuilds in Weekly / Monthly cycles.
"Been there, done that" is what many DBAs would respond. This is faster than redesigning a "COTS" schema and modifying the application code -- which would be part of your normal tuning exercise.
5. One key consideration is that the "perceived" cost of Index Block Splits is not significant.
No comments:
Post a Comment