Re: Yet Another Partitioned Table Question ... Example Included

Dereck L. Dietz wrote:
> 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.

One clarification requested.

Is it your intention to partition and create a primary key based on a column that will be updated and whose value will change over time?

If so I would not recommend it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org