Re: update global indexes

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 11 Nov 2004 19:40:30 +1100
Message-Id: <41932553$0$25121$afc38c87@news.optusnet.com.au>

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.

Yeah. I know. That's what I answered.

> I was not talking about rebuilding an index whenever needed.

I didn't think you were. Partition DDL *requires* you EITHER to rebuild an index or to UGI. You *have* to do one or the other. I know that. And I knew you knew that.

And as I said, whether you do one or the other depends. There's no right or wrong answer. And one is not better or worse than the other.

HJR > Thanks for the response and I got the answer. Received on Thu Nov 11 2004 - 02:40:30 CST