Re: using "alter index xxx unusable"

"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.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005