RE: Global Indexes on Large Partitioned Tables

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

? (The only thing that comes to mind is if you were worried having the date lead might create transiently hot blocks if many rows are inserted with the same date value concurrently.)

Now some questions about your question:

Does the update merely use the global index in the predicate, or are any of its columns updated?

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.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Seiler
Sent: Monday, April 14, 2008 11:06 AM
To: oracle-l
Subject: Global Indexes on Large Partitioned Tables

Running Oracle 10.2.0.3 on RHEL4 x86_64.

We have a range/hash partitioned table, around 250M rows, that we are seeing some recent performance slowdown on. This slowdown is occuring during a large batch UPDATE job. The routine is to select a large number of records from one table, and update corresponding records in this table based on that. Each job performs anywhere from 8-12 million updates.

The table itself is partitioned monthly by a DATE field, I'll call it foo_date. It is hash-subpartitioned by a VARCHAR2 field called foo_id
(despite the name, it is not a unique ID, but it is a pretty selective
value).

The kicker: the primary key for this table is a GLOBAL index on
(foo_id, event_id, foo_date). The UPDATE statement references all 3
of these fields and the primary key index is used in the query plan
(unique scan). We're looking a moving this to a local index, or at
least testing it.

Recently, however, time to finish the job has doubled and sometimes tripled. EM dbconsole shows a lot of db file sequential reads for this UPDATE statement. During the course of the batch job, the UPDATEs will affect rows in the partitions for the current cycle and 1 month prior.

I'm wondering if such a large volume of data would make a global index rather cumbersome to search through.

-- 
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l




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