Oradba Linux wrote:
> Howard J. Rogers wrote:
>
>> 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.
>>
>>
>>
> Internally what does update global indexes do ?
> I am not sure if anybody has learnt beyond the oracle's published docs
> regarding this.
What's to learn?
If I drop a partition of your table, what would *you* have to do to keep
the index usable? You would reply, I hope, 'delete all index entries
that pointed to the dropped partition'. And that's precisely what 'UGI'
does. And by tidying up the index like that, it keeps the index as a
whole usable.
Now postulate any partition DDL and see what you would have to do not
have chaos or crud in the index. UGI will do precisely that. Sometimes,
it removes index entries. Sometimes it alters their rowid pointers. It
does whatever is necessary, in short.
HJR
Received on Tue Nov 16 2004 - 00:09:55 CST
