Home » SQL & PL/SQL » SQL & PL/SQL » Partition a Table by List and Interval (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)
Partition a Table by List and Interval [message #572473] Wed, 12 December 2012 05:28 Go to next message
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
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: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
List-Interval composite partitioning does not exist in 11g.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#BABFDCFF

Regards
Michel

[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: 1943
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)
•Range-Range
•List-Range
•List-Hash
•List-List

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

•Interval-Hash
•Interval-List
•Interval-Range


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.

Kevin
Previous Topic: Can a oracle database Function return Images?
Next Topic: Merge statement - Intermediate commit
Goto Forum:
  


Current Time: Wed Oct 22 07:24:00 CDT 2014

Total time taken to generate the page: 0.09017 seconds