Enabling a table for partitioning

SQL Reference

SQL Reference : OpenEdge SQL Statements : ALTER TABLE : Migrating data from existing unpartitioned tables to partitioned tables : Enabling a table for partitioning

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

Use the following syntax to specify 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.

The below statement creates a table with two partition aligned indexes: IDX1 and IDX2 and an index IDX3 that is not partition aligned.

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);

The below ALTER TABLE statement converts the above table to a partitioned table by adding one data partition definition and specifies IDX1 as the local composite index. IDX2 and IDX3 are marked as global indexes. If the data in Pub.tpcustomer contains custid greater than 100000, then the statement returns an error.

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.

In the below example, Pub.sub_customer_list is LIST partitioned by zipcode and custid.

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;