Re: Should we be afraid of online index rebuild?

Hi,

We rebuild index online on DATAWAREHOUSE or OLTP database and we have never met any problem with it. Just be sure of you have enought space. If you rebuild on new tablespace, just check initial, next and maxextents clause for your extents.

Yel.

David Evans wrote:

> We have Oracle 8.1.7 on a production database, and we are thinking of
> rebuilding indexes online. Our main motivation is that we would not
> have to negotiate any additional maintenance window with the customer.
> However, our testing and our reading of items on Metalink has made us
> apprehensive. We were wondering if anybody has used the online index
> rebuild on a regular basis and can give us the benefit of their
> experience - should we be very afraid or not?
>
> Here are some of the things which made us worry:
>
> 1. We saw that an interruption of the index rebuild could lead to
> severe problems. In one test, an accidental deletion of a journal
> table that Oracle had in play led to the following results on the
> rebuild of an index subpartition:
>
> any attempt to use any part of the index returned an ORA 0600. In
> other words, the entire index was unusable. This was in spite of the
> fact that all subpartitions showed in the dictionary as 'usable'.
>
> In this case, we dropped and recreated the entire index. (Perhaps
> other solutions were available.) A search of Metalink turned up a
> case where someone else who interrupted an index rebuild had to
> manually reset a flag in Ind$ before he was even able to drop and
> recreate the index.
>
> 2. We were worried about possible performance problems. We are not
> sure what would happen if we rebuilt an index or part of an index
> while it was being updated heavily, and Oracle had to juggle writing
> to its journal table for the rebuild, building the index, and
> performing the updates. Our initial impulse is to avoid scheduling
> online rebuilds while heavy updating is taking place, but we aren't
> sure if this is precaution enough.
>
> 3. Oracle has to put a short-term initial lock on the table itself
> (or maybe, where applicable, just the partition/subpartition) before
> starting the rebuild. We were wondering if people had many problems
> with this.
>
>
> Can anybody give us any practical advice about this, before we find
> out the hard way.
>
> Thanks.
Received on Tue Feb 12 2002 - 16:37:51 CST