Re: update global indexes

Oradba Linux wrote:
>
> Howard J. Rogers wrote:
> > Oradba Linux wrote:
> >
> >
> >>Looking for opinions about using this option .
> >>How much better is this than rebuilding indexes after a partition is
> >>dropped?
> >>Version 9iR2 .
> >
> >
> > That's like asking whether apples are better than oranges.
> >
> > Update global indexes provides you with a way to keep a global index usable
> > despite partition DDL, at the cost of DDL statements no longer being quick
> > data dictionary operations. You have to evaluate whether that loss is worth
> > the gain of permanently usable global indexes.
> >
> > A rebuild takes exclusive locks. Update Global Indexes doesn't. Rebuilds
> > could be used to compact or move an index. UGI can't. A rebuild can let you
> > change PCTFREE or INITRANS. UGI can't.
> >
> > For ordinary, every day use, UGI wins hands down I think. But for a thorough
> > re-organisation, it doesn't even get close to a rebuild. It's a choice to
> > make. You can't say A is better than B. They're just different.
> >
> > Regards
> > HJR
> I was trying to decide whether to use UGI or rebuild global indexes
> after the partition is dropped as part of purge process.
> I was not talking about rebuilding an index whenever needed.
> Thanks for the response and I got the answer.

Its a percentage thing...If I've got two partitions, then I would (probably) do the rebuild because dropping one of them is 50% of the table.

If I've got a partition for each day of a year (ie, 365 of them) then UGI might very well be the best bet

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------