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 ?
No it won't, but that's the wrong question.
You partition things for ease of maintenance (as you have with your
table) or because there are performance benefits to be gained by doing
so (partition elimination during parsing, I/O placement on different
devices etc etc).
UGI must update the entire index, regardless of whether it is
partitioned or not.
But the real question here is: why bother to partition the table data
"for ease of data purging" and not partition any index on that table? If
you purge the table, the index must be purged too. Don't you think that
if partitioning helped in one case, it would help in the other?
HJR
> I did not test that.
>
>
Received on Fri Nov 19 2004 - 15:36:25 CST
