RE: RAC partitioning question
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Jan 2012 16:51:49 +0000
Tim,
I have asked them about the PK and if it could be eliminated (preferable), but
if that can't be done, then this is really a great idea. There is no trigger to
set the PK value so their code must do it, though I guess via a trigger it
could be modified to what we want.
Thank you for a great suggestion. (I've thought of similar solutions for other
problems, but it didn't occur to me on this one, I appreciate it!)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Tuesday, January 31, 2012 9:43 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: RAC partitioning question
Jed,
Why not get rid of the sequence-generated PK column and instead make another
NUMBER column the PK, itself generated both from the needed timestamp appended
to a sequence generated data value to ensure it's uniqueness? If you have
that, then you can RANGE partition on that NUMBER value according to your data
manipulation requirements and also have a LOCAL partitioned index so that you
have no GLOBAL index issues.
So, not sure what the scale/precision of the current PK column is, but let's
assume NUMBER(12), just for the sake of example? With the following code, you
can generate timestamp values synthesized with a sequence...
SQL> select
(to_number(to_char(systimestamp,'YYYYMMDDHH24MISSFF'))*1000000000)+timtest.nextval
nbr from dual;
NBR
----------------------------------
20120131112513714848000000011
Now, you can create your tables as follows...
create table xyz
(
tstamp number(30) not null,
/ (...other column definitions...)/
) partition by range (tstamp)
(partition p20120101 values less than (20120102000000000000)
tablespace P201201_DATA,
partition p20120102 values less than (20120103000000000000)
tablespace P201201_DATA,
/ ...and so on.../
partition p20141231 values less than (20150101000000000000)
tablespace P201412_DATA,
partition pmaxvalue values less than (maxvalue) tablespace
PMAX_DATA
);
create unique index xyz_pk on xyz(tstamp) local
(partition p20120101 tablespace P201201_INDX,
partition p20120102 tablespace P201201_INDX,
/ ...and so on.../
partition p20141231 tablespace P201412_INDX,
partition pmaxvalue tablespace PMAX_INDX
);
alter table xyz add constraint xyz_pk primary key (tstamp);
So now you have daily partitions, so if you wish to load or purge on a daily
basis, a simple DROP PARTITION does the job with no UPDATE GLOBAL INDEXES
issues (at least, not for a primary key). To get the timestamp, you'll need to
truncate off the trailing digits. The example above shows monthly tablespaces
to encourage tiered storage and READ ONLY tablespaces and optimization of
backups, but that is another post for another day. Other details to be worked
out, but that's the general idea...
Hope this helps...
--
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => PO Box 352151, Westminster CO 80035
website => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
On 1/31/2012 8:04 AM, Walker, Jed S wrote:
> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
