Re: Global Indexes on Large Partitioned Tables

Don,

I've recently dealt with a similar situation for some of our databases. The largest tables are partitioned by date and the indexes were originally created as global indexes.

The problem that was brought to my attention was the length of time it took to rebuild the indexes, which had to be done weekly as they were unusable after the regularly scheduled drop of older partitions. The indexes were recreated as local indexes and the pain stopped.

Use of local indexes should allow for index partition pruning which has the potential to significantly improve some of your queries. ymmv but a 10046 trace will show the partitions included in the read.

If you're don't have problems with unusable indexes yet, it is likely to become a problem eventually. Recreating the global indexes as local indexes should be advantageous on several fronts.

hth ... Robyn

On Mon, Apr 14, 2008 at 4:10 PM, Don Seiler <don_at_seiler.us> wrote:

> See replies inline below.
>
> On Mon, Apr 14, 2008 at 3:03 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> > Not an answer to your question, but I'm curious what the thinking was
> that
> > the global primary key does not match your partitioning strategy, as in
> >
> > foo_date,foo_id,event_id
>
> Unfortunately I can't say why the column order is what it is, or why
> it is a global index vs local, since the partitioning keys are
> involved. The developers told me that they ran some test when they
> designed it and the global index came out on top.
>
> > Does the update merely use the global index in the predicate, or are
> any of
> > its columns updated?
>
> None of the indexed columns are modified.
>
> > Have you recently increased in blevel on this index due to purging?
> >
> > If you're purging by foo_date annd foo_date is not well correlated with
> > foo_id and event_id, you have an opportunity to partially empty many
> blocks
> > that will not be the insert point in the index structure for newer
> entries.
>
> We don't delete records, but we do drop partitions after X many months.
>
> --
> Don Seiler
> http://seilerwerks.wordpress.com
> ultimate: http://www.mufc.us
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
I may not have gone where I intended to go, but I think I have ended up
where I needed to be.
Douglas Adams

--
http://www.freelists.org/webpage/oracle-l