Composite Partitioning [message #666110] |
Mon, 16 October 2017 03:19 |
|
Revathi.orafaq
Messages: 21 Registered: September 2017
|
Junior Member |
|
|
Hi All,
As per our business requirement we need to create the Composite Partitioning on one table .
Main Partitioning is LIST on branch column
and sub Partitioning is INTERVAL(DAY) on entry date column
I am able to create the LIST-RANGE(with out any interval) successfully .
But how to create the LIST -INTERVAL ?
Example Code :
DROP TABLE LIST_RANGE;
CREATE TABLE LIST_RANGE
(
BRANCH VARCHAR2(5),
ENTRY_DATE DATE
)
PARTITION BY LIST
(
BRANCH
)
SUBPARTITION BY RANGE
(
ENTRY_DATE
)
SUBPARTITION TEMPLATE
(
SUBPARTITION DT_15SEP2017 VALUES LESS THAN (TO_DATE(' 2017-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
SUBPARTITION DT_15OCT2017 VALUES LESS THAN (TO_DATE(' 2017-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
)
(
PARTITION ABC VALUES
(
'ABC'
)
);
If it is simple INTERVAL Partitioning its working fine.
CREATE TABLE LIST_INTERVAL
(
BRANCH VARCHAR2(5),
ENTRY_DATE DATE
)
PARTITION BY RANGE
(
ENTRY_DATE
)
INTERVAL
(
NUMTODSINTERVAL(1, 'DAY')
)
(
PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2019-06-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
DROP TABLE LIST_INTERVAL;
CREATE TABLE LIST_INTERVAL
(
BRANCH VARCHAR2(5),
ENTRY_DATE DATE
)
PARTITION BY LIST
(
BRANCH
)
SUBPARTITION BY RANGE
(
ENTRY_DATE
)
INTERVAL
(
NUMTODSINTERVAL(1, 'DAY')
)
SUBPARTITION TEMPLATE
(
SUBPARTITION DT_15SEP2017 VALUES LESS THAN (TO_DATE(' 2017-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
SUBPARTITION DT_15OCT2017 VALUES LESS THAN (TO_DATE(' 2017-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
)
(
PARTITION ABC VALUES
(
'ABC'
)
);
Can we use the INTERVAL in composite partitioning ?
Either in main partitioning or Sub partitioning can we use the INTERVAL ?
Please help me
Thanks
Revathi.T
|
|
|
|
|