Re: using "alter index xxx unusable"

From: Michael Peel <peelm_at_NOSPAMaccuread.com>
Date: Wed, 14 Dec 2005 11:50:40 GMT
Message-ID: <Xns972C78B151C60peelmNOSPAMaccureadc@194.6.79.66>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:dnmr2f$ed8$1_at_nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:

> "Michael Peel" <peelm_at_NOSPAMaccuread.com> wrote in message
> news:Xns972B9B411761DpeelmNOSPAMaccureadc_at_194.6.79.66...

>>
>> I'll do some benchmarking around marking the indexes as unsable, and
>> not. I was getting fairly large redo with the insert and the indexes
>> in tact, but I guess this redo is now associated with the index
>> rebuild. I'll mark the build as nologging and see what happens.
>>
>> Thanks again.
>>
>> M.
>>

>
>
> My oversight here - if you are planning to do the rebuild
> nologging, then you will notice a difference from leaving
> the truncated indexes in place. Following the append,
> the index inserts will be logged.
>
> A couple of warnings - make sure you test on a system
> that is running in archivelog mode (assuming your production
> system is running in archivelog mode) as there are some
> operations that Oracle doesn't bother to log when running
> in noarchivelog mode - you want to make sure you are
> testing something the way it will be in production.
>
> Also, make sure you include tests for the side effects
> of primary keys, unique keys, and foreign keys - and
> in the last case, what happens if someone tries to
> do something with a parent id while the child table
> is being reloaded.
>

Thanks for all the info...

This will be an instance that will be completely rebuilt overnight with no users allowed access during the rebuild.

We're even thinking off switching archiving off, as if there was ever a problem, we would just rebuild as usual.

This isn't really a production system, but a reporting tool for some of our external customers.

Thanks again,

M. Received on Wed Dec 14 2005 - 05:50:40 CST