17 March, 2008

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.

No comments: