Partition by three column values

Hi all,

I have a table structure similar to:

Acct_id, site_id, cr_date, ndc

This table will store approximately 700 million rows. There will be approximately 10 distinct acct_id and distinct site_id. Acct_id can have multiple site_id. Any query on the table will always include acct_id but may or may not include site_id, cr_date or ndc. New acct_id and site_id can be added to table later. I need to purge data from this table based on acct_id, site_id and cr_date. For example purge data for acct_id 1, site_id 1 older than 3 years, For act_id 1, site_id 2, purge data older than 4 years and act_id 2 site_id 3 purge data older than 5 years.

Given the table size and purging requirement, I should partition this table. How can I partition this table so that I can drop partition based on acct_id, site_id and cr_date? If I make multi column range partition on acct_id, site_id , cr_date , I can not add a new partition for new site_id for acct_id less than max(acct_id). E.g if I have partitions as
1,1, jan2006
2,2,jan2006

I can not add partition for 1, 3, jan2006

I can not create composite partition either, as sub partition can only be hash partitioned and I can not drop partitions based on acct_id, site_id and cr_date.

Can I really partition this table? Any suggestion is really appreciated.

Thanks

Sandeep

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