Altering partitioned tables without partition schema definition

Syntax to alter a table to be a partitioned table without any partition schema definition

ALTER TABLE table_name SET PARTITION [ TABLE ]
[ USING INDEX index_name, index_name, ...]

Syntax to create partition policies on a partitioned table without any partition schema definition

ALTER TABLE ...partition_policy
[(partition_policy_detail, [partition_policy_detail], ...)]

Parameters

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

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

Notes

To alter a partitioned table without a partition schema definition, the table must be empty and its indexes and LOB columns must be in the type II area. All the indexes specified in the USING INDEX clause are marked as local and must have common prefix keys; The rest of the indexes are marked as global.

Partition policy definitions can be also be added using the ALTER TABLE ADD PARTITION statement. If a table has only a row-based default index, then the default index is removed and a new default index with partitioned columns and row IDs is created.

Examples

Altering a table to be marked as a partitioned table without any partition schema definition

CREATE TABLE Order
(
orderid integer,
Item varchar (50),
Order_date date,
Country varchar (50),
State varchar (50),
City varchar (50))
AREA "area_type2";

CREATE INDEX order_idx1 ON Order (order_date)
AREA "area_type2";

CREATE INDEX order_idx2 ON Order (Item)
AREA "area_type2";

ALTER TABLE Order SET PARTITION
USING INDEX order_idx1;

Executing the above statement marks the Order table as a partitioned table without any partition schema definition and the index order_idx1 is marked as a local index.

Altering a partitioned table without any partition schema definition to be a partitioned table with partition policy definitions

The following example illustrates altering a partitioned table without any partition schema definition to a partitioned table with partition policy definitions using the ALTER TABLE ADD PARTITION statement:

ALTER TABLE Order PARTITION BY RANGE (order_date)
USING TABLE AREA "area_type2";
ALTER TABLE Order ADD
(
PARTITION P2014 VALUES <= (2014),
PARTITION P2015 VALUES <= (2015));