Yet Another Partitioned Table Question ... Example Included

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Wed, 29 Nov 2006 01:26:10 GMT
Message-ID: <Sk5bh.16751$9v5.16021@newssvr29.news.prodigy.net>

The following is working with Oracle 10g R2.

CREATE TABLE hist_group_subgrp_class
(

   sys_date DATE NOT NULL,
   user_action CHAR(1) NOT NULL,
   username VARCHAR2(30) NOT NULL,

   new_group_id   VARCHAR2(8),
   old_group_id   VARCHAR2(8),
   new_subgroup_id  VARCHAR2(5),
   old_subgroup_id  VARCHAR2(5),
   new_subgroup_region  VARCHAR2(4),
   old_subgroup_region  VARCHAR2(4),
   new_class_id   VARCHAR2(10),
   old_class_id   VARCHAR2(10),
   new_group_name  VARCHAR2(50),
   old_group_name  VARCHAR2(50),
   new_subgroup_name  VARCHAR2(80),
   old_subgroup_name  VARCHAR2(80),
   new_class_desc  VARCHAR2(100),
   old_class_desc  VARCHAR2(100)

)
PARTITION BY RANGE ( sys_date )
(

  PARTITION hist_grosubcla_q4_2006 VALUES LESS THAN
(TO_DATE('01-JAN-2007','DD-MON-YYYY')),

  PARTITION hist_grosubcla_q1_2007 VALUES LESS THAN
(TO_DATE('01-APR-2007','DD-MON-YYYY')),

  PARTITION hist_grosubcla_q2_2007 VALUES LESS THAN
(TO_DATE('01-JUL-2007','DD-MON-YYYY')),

  PARTITION hist_grosubcla_q3_2007 VALUES LESS THAN
(TO_DATE('01-OCT-2007','DD-MON-YYYY')),

  PARTITION hist_grosubcla_q4_2007 VALUES LESS THAN
(TO_DATE('01-JAN-2008','DD-MON-YYYY')),

  PARTITION hist_grosubcla_overflow VALUES LESS THAN ( MAXVALUE ) );

The above table will be populated using an INSERT/UPDATE/DELETE trigger on a master table.

I want to partition the table on the SYS_DATE column which would be the date/time that a row is either
added, modified or deleted from the master table. My intent is to partition the table with the view that
only a certain number of quarters (in this example 5) would be stored in history online and when a new
quarter arrived the oldest partition would be backed-up to DVD archive and then dropped from the online table.

I'm relatively inexperienced with partitioned tables (as my previous posts probably indicate) and I'm
not sure if I can -- or should -- partition the table on the SYS_DATE column yet have a primary key
consisting of SYS_DATE / USER_ACTION / USERNAME. I'm second guessing myself and starting to believe that a primary key of just SYS_DATE would provide me with a unique key per history row.

Another item I'm questioning is whether the SYS_DATE should be a DATE or TIMESTAMP. As for the primary key ... when a define it would it be best for me to define it as a local partitioned
index or not?

Once again, thanks. Received on Tue Nov 28 2006 - 19:26:10 CST