Splitting a Level-2 Partition for a Level-2 *-List Partitioned Table

For example, assume that the defined range of the level-2 partition product2_channel2 of a level-2 *-list partitioned table hash_list_sales is DEFAULT. You can specify a split point to split the level-2 partition into two partitions and update the global index.

ALTER TABLE hash_list_sales SPLIT SUBPARTITION product2_channel2 VALUES ('6', '7', '8', '9') INTO
(
    SUBPARTITION product2_channel2_p1, -- The first partition range is ('6', '7', '8', '9').
    SUBPARTITION product2_channel2_p2  -- The second partition range is DEFAULT.
) UPDATE GLOBAL INDEX;

Alternatively, split the partition product2_channel2 into multiple partitions without specifying a split point, and update the global index.

ALTER TABLE hash_list_sales SPLIT SUBPARTITION product2_channel2 INTO
(
    SUBPARTITION product2_channel2_p1 VALUES ('6', '7', '8'),
    SUBPARTITION product2_channel2_p2 VALUES ('9', '10'),
    SUBPARTITION product2_channel2_p3 -- The third partition range is DEFAULT.
) UPDATE GLOBAL INDEX;

Alternatively, split the partition by specifying the partition value instead of the partition name.

ALTER TABLE hash_list_sales SPLIT SUBPARTITION FOR (1200, '6') VALUES ('6', '7', '8', '9') INTO
(
    SUBPARTITION product2_channel2_p1, -- The first partition range is ('6', '7', '8', '9').
    SUBPARTITION product2_channel2_p2  -- The second partition range is DEFAULT.
) UPDATE GLOBAL INDEX;