Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Should we be afraid of online index rebuild?

Should we be afraid of online index rebuild?

From: David Evans <dave.evans_at_eds.com>
Date: 11 Feb 2002 11:32:11 -0800
Message-ID: <86b90c1a.0202111132.7e85faab@posting.google.com>


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 Mon Feb 11 2002 - 13:32:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US