Home » SQL & PL/SQL » SQL & PL/SQL » Partition a Table by List and Interval (Oracle Database 11g Enterprise Edition Release - 64bit Production)
Partition a Table by List and Interval [message #572473] Wed, 12 December 2012 05:28 Go to next message
Messages: 34
Registered: October 2005
Location: Bangalore
Dear All,

We have a table like below.

Create table Item_max
CCN varchar2(10),
Run_date date,
Item varchar2(10),
status varchar2(1)
partition by list(CCN)
partition A2C values('ABC'),
partition D2F values('DEF')

Currently we are inserting the cycle data for all Items every day into above table for all the CCN's.Currently there are 100k Items in our DB and gets inserted every day into above table with Different Rundates for all CCN's.

We need the above data for 6months and we would like to purge the data based on the RunDates.

We thought of creating a Partition on CCN and Interval Subpartition on Run_date with Interval of every 1 day but I could get any materials on the net to have List-Interval partitioning in Oracle 11G.

Also List - Range partitioning doesnt work here as the Dynamic partition used(using Maxused) will contain the data for all Run_dates instead of particular rundate.

Any help to resolve above would be greatly appreciated..
Re: Partition a Table by List and Interval [message #572486 is a reply to message #572473] Wed, 12 December 2012 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
List-Interval composite partitioning does not exist in 11g.



[Edit: add URL]

[Updated on: Wed, 12 December 2012 06:30]

Report message to a moderator

Re: Partition a Table by List and Interval [message #572514 is a reply to message #572486] Wed, 12 December 2012 12:02 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Extended Composite Partitioning

In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:

•Range-Hash (available since 8i)
•Range-List (available since 9i)

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:


What is difference between list-interval and interval-list. Do they not both create the same partitions in the end? Maybe I need to do more reading.

Previous Topic: How to display data in a required format by SQL Select Query?
Next Topic: How to get the file name from directory
Goto Forum:

Current Time: Thu Aug 24 00:06:54 CDT 2017

Total time taken to generate the page: 0.01770 seconds