Re: Global Indexes on Large Partitioned Tables

From: Don Seiler <don_at_seiler.us>
Date: Mon, 14 Apr 2008 15:10:29 -0500
Message-ID: <716f7a630804141310v426fd3a6i9dfd74c2dfb50925@mail.gmail.com>


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
Received on Mon Apr 14 2008 - 15:10:29 CDT

Original text of this message