Howard J. Rogers wrote:
> 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
Well here we are all making assumptions again ... but I have yet to find
the syntax 'update global index' in any of my Oracle books. So my
assumption was that it was the table that was being updated. And I'd
rather affect an indexes partition rather than the entire index.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Nov 20 2004 - 11:12:33 CST
