Re: Partition by three column values
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: sharmakdeep_oracle@xxxxxxxxx, dubey.sandeep@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 22 Feb 2006 07:45:43 -0800 (PST)
Forgot to mention that the subpartition template needs
to have entries for each subpartition:
SUBPARTITION "200501_1" values ( 200501_1 ),
SUBPARTITION "200502_1" values ( 200502_1 ),
..
SUBPARTITION "200601_1" values ( 200601_1 ),
SUBPARTITION "200602_1" values ( 200602_1 ),
..
SUBPARTITION "200501_2" values ( 200501_2 ),
SUBPARTITION "200502_2" values ( 200502_2 ),
..
If you have 10 distinct sites there would be 120
subpartitions for 1 year, and 1200 for 10 distinct
acct_ids. Multiply that with 5 years, we are talking
about 6000 total subpartitions. I don't know how you
define the retentions, and will this 6000 number be a
high-watermark or not.
Deepak
--- Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
wrote:
> 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.
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
