12.2
Is there an easy to add subpartitions to an existing partitioned table in 12.2
This is existing table
create table
t_already_there (
start_date DATE,
store_id NUMBER,
keep_flag varchar2(1)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION P1_DATE1 VALUES LESS THAN (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
I want to add sub_partition on keep_flag where values would go a Y subpartition if Y or if anything else go to a default subpartition. Could have several hundred partitions in tables Im doing this too.
If I created from new heres how it would look and this works
create table t_new
(
start_date DATE,
store_id NUMBER,
keep_flag varchar2(1)
)
PARTITION BY RANGE (start_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
SUBPARTITION BY LIST (keep_flag)
SUBPARTITION TEMPLATE
( SUBPARTITION p_t VALUES ('Y')
, SUBPARTITION to_others values (default)
)
(
PARTITION P1_DATE1 VALUES LESS THAN (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
yes I could create new table and either redefine or insert and rename tables but interested to see can we do an alter table modify and the subpartitions. I cant documentation that shows how. thanks