0

Currently, we use a query similar to the following to add partitions:

ALTER TABLE ${" + DDL_VARIABLE_KEY_SCHEMA_NAME + "}.${" +
  DDL_VARIABLE_KEY_TABLE_NAME + "} ADD PARTITION ${" +
  DDL_VARIABLE_KEY_PARTITION_NAME + "} VALUES LESS THAN(${" +
  DDL_VARIABLE_KEY_CUTOFF_DATE + "})"

How should I go about creating the query to create the sub-partitions?

Will something like the below suffice? or does it need any changes?

alter table xxxxx.xxxxx
modify PARTITION BY RANGE (CREATEDATE)
SUBPARTITION BY LIST (PURGEID)
subpartition template(
SUBPARTITION SP1 values (0),
SUBPARTITION SP2 values (1),
SUBPARTITION SP3 values (2),
SUBPARTITION SP4 values (3))
(
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('01-MAY-2018', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('01-SEP-2018', 'DD-MON-YYYY')),
PARTITION p5 VALUES LESS THAN (maxvalue)) online;

0 Answers0