Re: partitions

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 19 Nov 2004 08:01:34 +1100
Message-ID: <419d0dae$0$2681$afc38c87@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 Received on Thu Nov 18 2004 - 15:01:34 CST