Howard J. Rogers wrote:
> 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
You are correct ... that I can see ... but what I specifically wanted to
know was why the indexes weren't local to the partition ... a valid
reason being the one you pointed out: of course.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Nov 19 2004 - 19:17:31 CST
