The following ALTER TABLE syntax defines the partition of a table. Here, the actual data continues to reside in the composite initial partition and the newly added partitions point to the composite initial partition. The data can be accessed normally after the execution of this syntax.

Syntax

ALTER TABLE ...[ partition_definition ] 
  USING INDEX index_name [ , index_name, ...]

Parameters

partition_definition

Uses the following syntax:

PARTITION BY {RANGE|LIST} column_name [PRO_DESCRIPTION desc] [subpartition_definition,...] area_spec (partition_attribute [partition_attribute ], ...)

The partition_definition must contain values for all partition keys. If the partition keys are x, y, and z, then all the partition definitions must specify values for columns x, y, and z.

subpartition_definition

Uses the following syntax:

SUBPARTITION BY {RANGE|LIST} column_name [PRO_DESCRIPTION desc]

column_name

Uses the following syntax:

simple_column_name | (simple_column_name )

partition_attribute

Uses the following syntax:

PARTITION [partition_name ] VALUES { <= | IN } (column_values) [area_spec ] [ PRO_DESCRIPTION desc ]

partition_name

Specifies the SQL identifier.

column_values

Uses the following syntax:

column_value [ , column_value, ...]

column_value

Uses a constant as its value. Each column_value corresponds to a column name of a partition or a subpartition, in the same order in which the column names are defined.

area spec

Specifies the default areas for all partitions defined, if not overridden by specific partition definitions. The syntax for area spec is:

( ([ USING TABLE AREA area name] [ USING INDEX AREA area name] [ USING LOB AREA area name] ) | [ USING NO SPACE ])

index_name

Specifies the SQL identifier. The index must be a partition aligned index.

Specifying partition specific areas on LOB columns

ALTER TABLE ADD LOB_column...
 [ FOR PARTITION partition_name USING LOB AREA LOB_area ]...

Notes

• The ALTER TABLE statement for table partitioning is an online operation.
• Data of an unpartitioned table can be migrated to a partitioned table only if the table and all its indexes are in the type II area.
• Partition names are optional, unique across a database, and have the lexical properties of a table name; if not specified, system generated partition names are used.
• The maximum number of partitions per table is 32,767.
• A partition can be defined without any allocated data storage.
• Partition keys must be literal constants and must be enclosed in parentheses for LIST partitions. For RANGE partitions, the parentheses are optional.
• Table partitioning must be enabled for a database to use the table partitioning syntax with the ALTER TABLE statement.
• All index supported data types can be used as partition keys.
• The area definition at the partition level always takes priority among the area definitions provided at multiples levels.
• While migrating data from an unpartitioned table to a partitioned table, the ALTER TABLE statement validates the data in the existing table based on whether the defined partitions meet the following requirements:
  • Only one RANGE partition definition is allowed per table such that the single RANGE partition defined has a value greater than or equal to the column's maximum value. If the table contains subpartitions, then only one RANGE partition is allowed per list.
  • Any number of LIST partitions can be defined as long as all the data in the column belongs to the partitions defined. For any row that does not belong to the defined partitioned, the ALTER TABLE statement returns an error.
• For a subpartition, all the leading subpartition levels must define LIST partitions. The last subpartition level can either be a RANGE partition or a LIST partition.
• There can be up to 15 levels of subpartitioning.
• Partitioned key columns must not be repeated.
• If the partition definition has a RANGE column, then the <= clause must be used to specify the partition key values, otherwise use the IN clause.
• For RANGE partitions having the same key values, the values must be specified sequentially in an ascending order.
• The following semantics apply when using the FOR PARTITION clause in the ALTER TABLE statement to add LOB columns:
  • The table specified in the statements must be a partitioned table
  • The partition specified in the FOR PARTITION clause must be an existing partition in the table
  • Partitions can be specified in any order
  • The specified area must be present in the table and in the type II area
  • Specification of any area other than the LOB area (for example, table area) results in an error

Examples

Using ALTER TABLE with partitioned tables

The following example illustrates how to use the ALTER TABLE statement with partitioned tables.

CREATE TABLE Pub.tpcustomer 
(    Custid  int,
    Custname varchar (50),
    join_date date,
    salary int
);
CREATE INDEX IDX1 on Pub.tpcustomer (custid);
CREATE INDEX IDX2 on Pub.tpcustomer (custid,salary);
CREATE INDEX IDX3 on Pub.tpcustomer (salary);

ALTER TABLE Pub.tpcustomer
 PARTITION BY RANGE Custid 
  USING TABLE AREA "custtab_area" 
  USING INDEX AREA "custidx_area" (  PARTITION p3 VALUES <= (100000))  
  USING INDEX IDX1;

Using ALTER TABLE with subpartitioned tables

The following example illustrates how to use the ALTER TABLE statement with subpartitioned tables.

CREATE TABLE Pub.sub_customer_list
 (   Custid  int,
    Custname varchar(50),
    Zipcode int
 )

 AREA "TP area";
 

CREATE INDEX sub_customer_list_list_idxlc1 
  ON pub.sub_customer_list_list (Zipcode, Custid, Custname)
  AREA "TP idx area";

CREATE INDEX sub_customer_list_list_idxlc2 
 ON pub.sub_customer_list_list (Zipcode, Custid) 
 AREA "TP idx area";

CREATE INDEX sub_customer_list_list_idxg1 

 ON pub.sub_customer_list (Custid) 
 AREA "TP idx area";

ALTER TABLE pub.sub_customer_list
 PARTITION BY LIST Zipcode
 SUBPARTITION BY LIST Custid 
 USING TABLE AREA "TP Area" 
 ( 
  PARTITION sub_customer_lista1 VALUES IN (28019,801) ,
  PARTITION sub_customer_lista2 VALUES IN (28019,802) ,
  PARTITION sub_customer_listab VALUES IN (28019,803) ,
  PARTITION sub_customer_listb1 VALUES IN (28028,1804) ,
  PARTITION sub_customer_listb2 VALUES IN (28028,1805) ,
  PARTITION sub_customer_listb3 VALUES IN (28028,1806) ,
  PARTITION sub_customer_listc1 VALUES IN (28039,2807),
  PARTITION sub_customer_listc2 VALUES IN (28039,2808),
  PARTITION sub_customer_listc3 VALUES IN (28039,2809) )
  USING INDEX  sub_customer_list_idxlc1, sub_customer_list_idxlc2;