| Partition a Table by List and Interval [message #572473] |
Wed, 12 December 2012 05:28  |
prashanth7582
Messages: 30
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 #572514 is a reply to message #572486] |
Wed, 12 December 2012 12:02  |
 |
Kevin Meade
Messages: 1782
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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
