Oracle Database Discussions

Hi,

Can some one help us with advice/recommendation for domain index rebuilds. i.e. is it daily, weekly, monthly? or it is system dependent?

I have searched the web quite extensively I could find recommendations about why we shouldn't rebuild indexes! but nothing on the Domain indexes though.

Currently we are running everyday and recently its overrunning slightly into business hours. This rebuild was put in place quite sometime back because of a performance issue. Apparently this fixed the issue. We are hesitant to take this job offline dreading that it might bring the system to a halt.

I come from a DBA group who doesn't believe in index rebuilds.

To quote Tom Kyte on this
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

"So, what do you think was happening here? Well, the fact is that indexes, like people, have a certain "weight" they like to be at. Some of us are chubby -- some skinny -- some tall -- some short. Sure, we can go on a diet -- but we tend to gravitate BACK to the weight we were. The same is true for indexes -- what happened to this person is their index wanted to be wide and fat and EVERY MONTH they rebuilt it (put it on a diet). It would spend the first half of the month then getting fat again and generating gobs of redo due to the block splits it was undergoing to get there.

until the system got back to where the system actually wanted to be. And then -- AND THEN -- they (the dba's) would do it all over again!!!! They would destroy the equilibrium that the system worked so hard to get to. Bravo!! "

And there are many Oracle Aces who would agree with the above quote!


Any pointers is much appreciated.

Cheers
Niki

P.S We are running Oracle 10.2.0.4 on Windows 64 bit machine.