RAC partitioning question

From: Walker, Jed S <Jed_Walker_at_cable.comcast.com>
Date: Tue, 31 Jan 2012 15:04:33 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C56261DC9_at_COPDCEXMB08.cable.comcast.com>



Hi everyone,
I have an 11.2.0.3.0 RAC cluster. We have a table (in 12 schemas) that gets a decent amount of rows (depending on schema up to ~1m per day) and that we need to clean. Currently this is being done by a job that just deletes the rows in chunks. This of course, produces a lot of redo and causes extra load on the system. I'm looking into partitioning it and have some questions.

I have read that hash-partitioning is great for RAC; however, the purpose of this partitioning is to make removing old data easier. I also need to be sure that we never are in a situation where the "next" partition is not available. As such I was planning on using interval partitioning on the "time" column (number datatype in unix milliseconds).

About the table

  1. The table has a sequence generated Primary key.
  2. We want to cleanup up rows that are not current (<sysdate-n) on a column in the table
  3. The table is heavily used.

As such, the Primary Key has to be a global index. My plan is to drop the partitions as they get old (except the anchor partition of course), but my concern is the impact of the "update indexes" work to keep the global PK usable. Is the "update indexes" done online while the table is in use? I can't shut down the application to do this work, so I'm wondering how big an impact "update indexes" might have or if there are better ways to do this?

I don't see a need for the sequence based primary key so I might suggest eliminating it if possible.

I'd love to get opinions and experience on this?

(Oh, and I wish I had a test system ... yep)

Thanks,

Jed

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 31 2012 - 09:04:33 CST

Original text of this message