RE: Partition by three column values
- From: "Jim Silverman" <jsilverman@xxxxxxxxxxxxx>
- To: <dubey.sandeep@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Feb 2006 17:25:27 -0500
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...)
=====================================
Jim Silverman
Senior Systems Database Administrator
Solucient, LLC
Telephone: 734-669-7641
FAX: 734-930-7611
E-Mail: jsilverman@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandeep Dubey
Sent: Tuesday, February 21, 2006 4:48 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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)
Other related posts:
