Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partition by three column values

RE: Partition by three column values

From: Jim Silverman <>
Date: Tue, 21 Feb 2006 17:25:27 -0500
Message-ID: <2D1EFB433A527D4681B80C94B71E3598041ADF3B@aaexchange01.HEALTH.AD>

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

-----Original Message-----

[] On Behalf Of Sandeep Dubey Sent: Tuesday, February 21, 2006 4:48 PM To:
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

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.


-- 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)

-- Received on Tue Feb 21 2006 - 16:25:27 CST

Original text of this message