We are using somewhat similar concept in one of our tables (~500GB in size, 1.8B rows), where we range-partition for partition-elimination purposes, and List-subpartition for retention purposes. In your case you can do the following: Create a range-partiiton on Acct_id, and list-subpartiiton on RET_KEY (described later): PARTITION BY RANGE (ACCT_ID) SUBPARTITION BY LIST (RET_KEY) SUBPARTITION TEMPLATE ( SUBPARTITION OTHERS values ( DEFAULT ) ) (Partition P1 Values Less Than (2), Partition P2 Values Less Than (3), Partition P3 Values Less Than (4) ... ) where RET_KEY is an additional column for your table, populated using the values in site_id and cr_date. Eg: Site_id=1, cr_date=Nov2005, ret_key=200511_1 Site_id=1, cr_date=Jan2006, ret_key=200601_1 Site_id=2, cr_date=Nov2005, ret_key=200511_2 Site_id=2, cr_date=Jan2006, ret_key=200601_2 Site_id=3, cr_date=Feb2005, ret_key=200502_3 Site_id=3, cr_date=Mar2005, ret_key=200603_3 You could very well name the subpartitions as: P<acct_id>_<ret_key> P1_200511_1 : (ACCT_ID=1, SiteId=1, CR_DATE=200511) P2_200602_3 : (ACCT_ID=2, SiteId=3, CR_DATE=200602) So, just by looking at the subpartiiton name, you will know which Site_id and what cr_date data you can drop. The RET_KEY can be populated in various ways, say by ETL process, through triggers etc. Thanks, Deepak --- Sandeep Dubey <dubey.sandeep@xxxxxxxxx> wrote: > 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. > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- //www.freelists.org/webpage/oracle-l