ALTER TABLE...ADD PARTITION v17
Use the ALTER TABLE… ADD PARTITION
command to add a partition to an existing partitioned table. The syntax is:
ALTER TABLE <table_name> ADD PARTITION <partition_definition>; Where partition_definition is: {<list_partition> | <range_partition>} and list_partition is: PARTITION [<partition_name>] VALUES (<value>[, <value>]...) [TABLESPACE <tablespace_name>] [(<subpartition>, ...)] and range_partition is: PARTITION [<partition_name>] VALUES LESS THAN (<value>[, <value>]...) [TABLESPACE <tablespace_name>] [(<subpartition>, ...)] Where subpartition is: {<list_subpartition> | <range_subpartition> | <hash_subpartition>} and list_subpartition is: SUBPARTITION [<subpartition_name>] VALUES (<value>[, <value>]...) [TABLESPACE <tablespace_name>] and range_subpartition is: SUBPARTITION [<subpartition_name>] VALUES LESS THAN (<value>[, <value>]...) [TABLESPACE <tablespace_name>] and hash_subpartition is: SUBPARTITION [<subpartition_name>] [TABLESPACE <tablespace_name>] | SUBPARTITIONS <num> [STORE IN ( <tablespace_name> [, <tablespace_name>]... ) ]
Description
The ALTER TABLE… ADD PARTITION
command adds a partition to an existing partitioned table. There's no upper limit to the number of defined partitions in a partitioned table.
New partitions must be of the same type (LIST
, RANGE
, or HASH
) as existing partitions. The new partition rules must reference the same column specified in the partitioning rules that define the existing partitions.
You can use the ALTER TABLE… ADD PARTITION
statement to add a partition to a table with a DEFAULT
rule. However, there can't be conflicting values between existing rows in the table and the values of the partition to add.
You can't use the ALTER TABLE… ADD PARTITION
statement to add a partition to a table with a MAXVALUE
rule.
You can alternatively use the ALTER TABLE… SPLIT PARTITION
statement to split an existing partition, effectively increasing the number of partitions in a table.
Specify RANGE
partitions in ascending order. You can't add a new partition that precedes existing partitions in a RANGE
partitioned table.
Include the TABLESPACE
clause to specify the tablespace in which the new partition resides. If you don't specify a tablespace, the partition resides in the default tablespace.
Use the STORE IN
clause to specify the tablespace list across which the autogenerated subpartitions are stored.
If the table is indexed, the index is created on the new partition.
To use the ALTER TABLE... ADD PARTITION
command, you must be the table owner or have superuser or administrative privileges.
Parameters
table_name
The name (optionally schema-qualified) of the partitioned table.
partition_name
The name of the partition to create. Partition names must be unique among all partitions and subpartitions and must follow the naming conventions for object identifiers.
subpartition_name
The name of the subpartition to create. Subpartition names must be unique among all partitions and subpartitions and must follow the naming conventions for object identifiers.
(value[, value]...)
Use value
to specify a quoted literal value or comma-delimited list of literal values by which rows are distributed into partitions. Each partitioning rule must specify at least one value
. There's no limit on the number of values specified in a rule. value
can also be NULL
, DEFAULT
if specifying a LIST
partition, or MAXVALUE
if specifying a RANGE
partition.
For information about creating a DEFAULT
or MAXVALUE
partition, see Handling stray values in a LIST or RANGE partitioned table.
num
The SUBPARTITIONS num
clause is supported only for HASH
and can be used to specify the number of hash subpartitions. Alternatively, you can use the subpartition definition to specify individual subpartitions and their tablespaces. If you don't specify SUBPARTITIONS
or SUBPARTITION DEFINITION
, then the partition creates a subpartition based on the SUBPARTITION TEMPLATE
.
tablespace_name
The name of the tablespace in which a partition or subpartition resides.
example_adding_a_partition_to_a_list_partitioned_table example_adding_a_partition_to_a_range_partitioned_table example_adding_a_partition_with_subpartitions_number
- On this page
- Description
- Parameters