"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.
Received on Thu Nov 18 2004 - 19:22:36 CST
