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.
Received on Fri Nov 19 2004 - 06:04:49 CST
