Adding a new partition to a [range | list | interval]-range partitioned table is as described previously. The database automatically creates interval partitions for an interval-range partitioned table when data is inserted in a specific interval. You can specify a
SUBPARTITION clause for naming and providing ranges for specific subpartitions. If no
SUBPARTITION clause is specified, then the partition inherits the subpartition template specified at the table level. If there is no subpartition template, then a single subpartition with a maximum value of
MAXVALUE is created.
Example 4-27 adds a range partition
p_2007_jan to the range-range partitioned table
shipments, which is populated with data for the shipments ordered in January 2007. There are three subpartitions. Subpartitions inherit the compression attribute from the partition level and are stored in a compressed form in this example:
Example 4-27 Adding partitions to a range-range partitioned table
ALTER TABLE shipments ADD PARTITION p_2007_jan VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')) COMPRESS ( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy')) , SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy')) , SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy')) ) ;
You use the
SUBPARTITION clause of the
TABLE statement to add a range subpartition to a [range | list | interval]-range partitioned table. For an interval-range partitioned table, you can only add partitions to range or interval partitions that have been materialized.
The following example adds a range subpartition to the
shipments table that contains all values with an
order_date in January 2007 and a
delivery_date on or after April 1, 2007.
ALTER TABLE shipments MODIFY PARTITION p_2007_jan ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE) ;