Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Should we be afraid of online index rebuild?
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:
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