Global Indexes on Large Partitioned Tables
Date: Mon, 14 Apr 2008 10:06:27 -0500
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
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-lReceived on Mon Apr 14 2008 - 10:06:27 CDT