Range Partitioned table for table only holding 6 months of data

This table will only ever hold six months of data. It will be heavily loaded. I wanted to use a range partition to disperse the data across the partitions. The table will hold about 40 million rows.

It will be queried often using
Where TRANSACTION_DTM between X and Y

sometimes
Where (TRANSACTION_DTM between X and Y) and column_b = b

sometimes
Where (TRANSACTION_DTM between X and Y) and column_c = c

sometimes
Where (TRANSACTION_DTM between X and Y)  and column_b = b and column_c = c


CREATE TABLE mytable  (
    id number NOT NULL enable,
    transaction_dtm timestamp default systimestamp not null enable)
partition by range (mod(extract(month from transaction_dtm), 6))
  (Partition acl_aud_mes_part_1 values less than 1,
  partition ACL_AUD_MES_PART_2 values less than 2,
  Partition acl_aud_mes_part_3 values less than 3,
  Partition acl_aud_mes_part_4 values less than 4,
  Partition acl_aud_mes_part_5 values less than 5,
  Partition acl_aud_mes_part_6 values less than 6
  )
  ;

alter table mytable  add constraint XPKmytable  primary key (ID)
  using index (create index XPKmytable on mytable(AUDIT_MESSAGE_ID) reverse);

This syntax doesn't work. I'm hoping for both advice and the correct syntax.

Thanks,

G
gswitzAsked:
Who is Participating?
 
JPrzybyszewskiCommented:
Hi

I assume that you are using Oracle 11.2 and therefore we can apply Virtual Column Partitioning. Then the correct code should be:

drop table mytable;

CREATE TABLE mytable  (
    id number NOT NULL enable,
    transaction_dtm timestamp default systimestamp not null enable,
    part_key as (mod(extract(month from transaction_dtm), 6)) )
partition by range (part_key)
  (Partition acl_aud_mes_part_1 values less than (1),
  partition ACL_AUD_MES_PART_2 values less than (2),
  Partition acl_aud_mes_part_3 values less than (3),
  Partition acl_aud_mes_part_4 values less than (4),
  Partition acl_aud_mes_part_5 values less than (5),
  Partition acl_aud_mes_part_6 values less than (6)
  )
 ;

alter table mytable  add constraint XPKmytable  primary key (ID)
  using index (create index XPKmytable on mytable(ID) reverse);


In the index creation statement the column name was wrong.

Cheers,
Jarek
0
 
gswitzAuthor Commented:
Jarek, thanks for your solution. Will part_key take up space in the database? just curious (on disk?). I wasn't sure if this was an example of a virtual column.

Thanks,

Geoff
0
 
JPrzybyszewskiCommented:
No it want. For more details you may want to check the documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07254

Regards,
Jarek
0
 
gswitzAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.