DA Morgan 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.
>
>
> Absolutely!
No it won't. Read the question again. Will partitioning a global index
make 'update global indexes' any faster/easier/better than 'update
global indexes' on a glob non-partitioned index? Answer: Absolutely NOT,
because in either case the entire index must be subject to maintenance.
Regards
HJR
Received on Sat Nov 20 2004 - 01:58:48 CST
