Re: RAC partitioning question

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 31 Jan 2012 09:42:48 -0700
Message-ID: <4F281A08.8080004_at_evdbt.com>

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_at_EvDBT.com
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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>



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

Received on Tue Jan 31 2012 - 10:42:48 CST