Adding a new partition to a [range | list | interval]-list partitioned table is as described previously. The database automatically creates interval partitions as data for a specific interval is inserted. You can specify SUBPARTITION
clauses for naming and providing value lists for the subpartitions. If no SUBPARTITION
clauses are specified, then the partition inherits the subpartition template. If there is no subpartition template, then a single default subpartition is created.
The statement in Example 4-26 adds a new partition to the quarterly_regional_sales
table that is partitioned by the range-list method. Some new physical attributes are specified for this new partition while table-level defaults are inherited for those that are not specified.
Example 4-26 Adding partitions to a range-list partitioned table
ALTER TABLE quarterly_regional_sales ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY')) STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING ( SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'), SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'), SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'), SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX') );
You use the MODIFY
PARTITION
ADD
SUBPARTITION
clause of the ALTER
TABLE
statement to add a list subpartition to a [range | list | interval]-list partitioned table. For an interval-list partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.
The following statement adds a new subpartition to the existing set of subpartitions in the range-list partitioned table quarterly_regional_sales
. The new subpartition is created in tablespace ts2
.
ALTER TABLE quarterly_regional_sales MODIFY PARTITION q1_1999 ADD SUBPARTITION q1_1999_south VALUES ('AR','MS','AL') tablespace ts2;