?
Solved

Range Partitioned table for table only holding 6 months of data

Posted on 2011-10-13
4
Medium Priority
?
294 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:gswitz
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
JPrzybyszewski earned 2000 total points
ID: 36967199
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
 

Author Comment

by:gswitz
ID: 36968726
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
 
LVL 3

Expert Comment

by:JPrzybyszewski
ID: 36968899
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
 

Author Closing Comment

by:gswitz
ID: 36969616
Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question