| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partition by three column values
Actually, Sandeep, you can create the partitions as you described. You
would issue the command
alter table split partition <part_name> ...
where <part_name> is the name of the partition whose key will be immediately "greater" than those of the new partition you are trying to insert. (You'd have to check the syntax for the ALTER TABLE statement...)
FAX: 734-930-7611 E-Mail: jsilverman_at_solucient.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandeep Dubey
Sent: Tuesday, February 21, 2006 4:48 PM
To: oracle-l_at_freelists.org
Subject: 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
This message is a private communication. It may contain information that is confidential
and legally protected from disclosure. If you are not an intended recipient, please do
not read, copy or use this message or any attachments, and do not disclose them to others.
Please notify the sender of the delivery error by replying to this message, and then
delete it and any attachments from your system.
Thank you,
Solucient LLC
(eXclaimer 4x)
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 21 2006 - 16:25:27 CST
 |
 |