Re: Partition by three column values

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Tue, 21 Feb 2006 21:04:07 -0800 (PST)
Message-ID: <20060222050407.81689.qmail@web52803.mail.yahoo.com>

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_at_gmail.com> 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

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

Received on Tue Feb 21 2006 - 23:04:07 CST