Rebuilding Indexes - When and Why ?

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.

Rebuilding Indexes

Conventional "modern" ("modern" meaning "developed between 5 and 10 years ago ?) wisdom is that indexes in Oracle, generally, do not need to be rebuilt. This sometimes takes more strident expressions as "indexes never need to be rebuilt" . At times this position is expressed with the same passion as current opinions about BCHRs and RAID-5. However, saner opinions do qualify the assertion.

I put it that there are situations and systems / implementations where, not only is it necessary and meaningful to rebuild indexes, but it makes sense to rebuild them frequently. Such a statement from a DBA who has not gained respect in the community invariably meets responses ranging from "no, you do not need to rebuild indexes" to "tell me why you think that you need to rebuild indexes" to "this is a [false] silver bullet to performance issues".

The real problem with the whole idea of rebuilding indexes is that a) either the new DBA simplifies this to mean "rebuild all [or most] [or largest] indexes frequently [weekly/monthly]" or b) the expert assumes that the DBA has already made this simplification. Simply because there is inadequacy in the message being communicated by either party.

How I would express the proper statements about rebuilding indexes would be something like :

{I will be explaining my statements in updates to this posting and/or future postings.
I've posted my first set of updates as "Rebuilding Indexes - When and Why ?"}

If anyone asks if indexes should be rebuilt or even says that he
rebuilds indexes "for performance", the proper response,in my
opinion, should be a measured "it depends".
To someone saying "whether indexes should be rebuilt" we should
respond : "it depends", "possibly".
To someone saying ""we rebuild indexes and get better performance"
we should respond "possibly, depending on the characteristics of
the data and how it is inserted and purged, and what queries are
executed".

Rebuilding indexes DOES correct performance problems.
Even regularly rebuilding indexes.
Provided you know which indexes need to be rebuilt and when they
need to be rebuilt.
(The correct answers are "Not ALL indexes, but specific indexes
with certain characteristics", "On the occurrence of the
triggering event {eg a large purge as is done by monthly /
quarterly batch jobs even in OLTP systems} ").

The DBA might have already "been there, done that".
He has *proven* to himself that he gets a performance improvement.

If you have a system where batch runs must finish within a
specific time (have to meet an SLA) and you find that rebuilding
indexes after large purges actually does bring batch jobs within
the SLA you would measure that against the "trickle" effect of
index block splits during normal oltp transactions.

As a rule, I, too, dislike a general rebuild of indexes unless the
requirements above are met.
However, when the CFO / Upper Management paying for the hardware,
software and DBA salaries find that you cannot meet your SLA until
and unless you rebuild indexes once a month, they wouldn't be
interested in knowing about the internals of index structures and
the "slow poision" ("death by a thousand cuts") impact of block
splits.
Heck, the the system get's resurrected every month by rebuilding
the *correct* indexes. That is what matters.

Index block splits are for DBAs and computer science classes.

On the other hand, if you have no SLA commitments and/or you really
do not have a system with performance constraints, an index
rebuild makes no sense.

So, to the list of candidate scenarios where index rebuilds can be
useful, I would add SLAs. And that is what does happen with
complex ERP systems (eg like the ones the OP mentioned) from 3rd
party vendors -- those there you cannot modify the programs.

ALTER TABLE ... SHRINK SPACE

It took me some time (as I built the test and validation scripts iteratively) to setup a test to evaluate the ALTER TABLE ... SHRINK SPACE command available in Oracle 10g. I ran these tests (see the document) on 32-bit 10.2.0.3 on Windows XP on my home PC.
(I couldn't extend the tests to larger sizes as "testing the tests" themselves would take signficant time on my PC !).
It seems as if, besides, reorganising the usage of blocks within a table, the command can also identify and drop extents (seems to have been intelligent enough to drop the last 3 extents although rows had been deleted from random blocks across multiple extents).

Update : There is an Oracle Bug logged for the high Undo and Redo generated by an ALTER TABLE ... SHRINK SPACE command. Bug #3888229 "HUGE REDO AND UNDO GENERATED DURING A TABLE SHRINK OPERATION. "

Update 10-Mar-08 : I have added Block, Redo and Undo statistics for the SHRINK operation. These indicate signficant activity.
I've also run a test with Uniform Size 128K instead of AutoAllocate. At the current table size, there is no significant difference.
The document (link above) has been updated.