Oradba Linux wrote:
> Oradba Linux wrote:
>
>> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
>> news:419d0dae$0$2681$afc38c87_at_news.optusnet.com.au...
>>
>>> Oradba Linux wrote:
>>>
>>>> DA Morgan wrote:
>>>>
>>>>
>>>>> Oradba Linux wrote:
>>>>>
>>>>>
>>>>>> I have a partitioned table and only global indexes. When i do a count
>>>>>> on a single partition, it uses the global index and accesses the
>>>>>> table. It is painfully slow. Is there a way to force it fullscan a
>>>>>> partition alone?
>>>>>> Also how do i know if the global non partitioned index is in a
>>>>>> unusable state after the ddl on the partitions without using "update
>>>>>> global indexes" clause.
>>>>>>
>>>>>> Oracle 9204 / Sun os 32 bit
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Why global indexes? It pretty much defeats the entire point of using
>>>>> partitions? And is the cause of the issue you face.
>>>>>
>>>>> BTW: If you have current statistics ... why not query the data
>>>>> dictionary?
>>>>
>>>>
>>>> Daniel
>>>>
>>>> We are on an OLTP system where data in fast growing tables need to be
>>>> purged on frequent basis. We opted to do partitioning . Tests indicate
>>>> the drop partition with UGI is slow but does not cause a downtime. I
>>>> assume lot of shops wanted to do this way and could be reason for
>>>> Oracle
>>>> to come up with UGI. I may be wrong.
>>>
>>>
>>>
>>> You aren't. Rebuilding an unusable index is a lot of I/O and a lot of
>>> exclusive table locking. Naturally there was a need for a cheap
>>> alternative (relatively cheap, that is).
>>>
>>> But Daniel's question wasn't about why you would use UGI. It was why
>>> you'd design to use a global index on a partitioned table in the first
>>> place.
>>>
>>> And I think that question came about because Daniel has assumed you mean
>>> a global non-partitioned index. I'm sure he can see a use for global
>>> partitioned indexes (ie, indexes partitioned in a way that doesn't match
>>> the way the table itself is partitioned).
>>>
>>> Regards
>>> HJR
>>
>>
>>
>> My scenario is i have range partitioned on date column.. This can make
>> the
>> data purge easy.
>> But on the other hand most of my queries are driven against an another
>> column, so i preferred to use a global
>> index. This global index is non-partitioned. Will partitioning this index
>> help during UGI ?
>> I did not test that.
>>
>>
> The downside of UGI is generates lots of redo and undo. For a 1.7GB
> Global Index it generated about 800MB of redo when i dropped a partition
> that is 1/3 size of all partitions.
That isn't a "downside". It's an inevitable consequence of actually
*understanding* what UGI does. If you're going to modify real data
blocks, be prepared to have to produce all relevant redo and undo.
The "upside", if you insist on thinking in those terms, is that your
index remains usable throughout, and doesn't become unusable.
Those are not actually ups and downsides, however: just features which
have to weighed before making intelligent decisions about which to use.
HJR
Received on Fri Nov 19 2004 - 15:38:32 CST
