Examples

SQL Reference

SQL Reference : OpenEdge SQL Statements : ALTER TABLE : Migrating data from existing unpartitioned tables to partitioned tables : Examples

Examples

Migrating an unpartitioned table to a partitioned table

The ALTER TABLE statement in the following example migrates data of an unpartitioned table to a partitioned table. The table in the example contains three columns and three indexes (out of which two are partition aligned):

CREATE TABLE Pub.tpcustomer
(
Custid int,
Custname varchar (50),
Zipcode int
)
AREA "custtab_area";

CREATE INDEX Customer_idx_local1 on Pub.tpcustomer
(zipcode)
AREA "custidx_area";

CREATE INDEX Customer_idx_local2 on Pub.tpcustomer
(zipcode,custid)
AREA "custidx_area";

CREATE INDEX Customer_idx_globa1 on Pub.tpcustomer (Custid) AREA "custidx_area";

INSERT INTO Pub.tpcustomer VALUES (801, 'cust1', 28019);

INSERT INTO Pub.tpcustomer VALUES (802, 'cust2', 28019);

INSERT INTO Pub.tpcustomer VALUES (803, 'cust3', 28019);

INSERT INTO Pub.tpcustomer VALUES (1804, 'cust4',28028);

INSERT INTO Pub.tpcustomer VALUES (1805, 'cust5',28028);

INSERT INTO Pub.tpcustomer VALUES (1806, 'cust6',28028);

INSERT INTO Pub.tpcustomer VALUES (2807, 'cust7',28039);

INSERT INTO Pub.tpcustomer VALUES (2808, 'cust8',28039);

INSERT INTO Pub.tpcustomer VALUES (2809, 'cust9',28039);

ALTER TABLE Pub.tpcustomer

PARTITION BY LIST zipcode

USING TABLE AREA "custtab_area"
USING INDEX AREA "custidx_area"
(
PARTITION ZIP_28019 VALUES IN (28019),
PARTITION ZIP_28029 VALUES IN (28028),
PARTITION ZIP_28039 VALUES IN (28039)
)
USING INDEX Customer_idx_local1, Customer_idx_local2;

The above ALTER TABLE statement creates partition definition of the table with the data in the composite initial partition. All the three newly added partitions point to the composite initial partition. The data can be accessed normally.

The following ALTER TABLE SPLIT statement marks all the three partitions as ready to be split. After the statement is executed, the data access to these partitions results in an error.

ALTER TABLE Pub.tpcustomer PREPARE FOR SPLIT PRO_INITIAL;

The following statement moves the data to its respective partitions. At this stage, each partition contains three rows and the table is completely partitioned with three LIST partitions.

$PROUTIL testdb -C partitionmanage split table Pub.tpcustomer composite initial
useindex Customer_idx_local1

Migrating data of an unpartitioned table to a RANGE partitioned table

The following statement creates a table with four indexes, out of which, the first two are partition aligned.

The below statement alters the table to have a single RANGE partition:

CREATE TABLE Pub.tpcustomer
(
Custid int,
Custname varchar(50),
join_date date,
salary int
)
AREA "Customer_Table_Area";

CREATE INDEX tpcustomer_idxc21 on pub.tpcustomer(Custid) AREA "Customer_Table_Area";

CREATE INDEX tpcustomer_idxc22 on pub.tpcustomer(Custid, Custname) AREA "Customer_Table_Area";

CREATE INDEX tpcustomer_idxg21 on pub.tpcustomer(join_date) AREA "Customer_Table_Area";

CREATE INDEX tpcustomer_idxg22 on pub.tpcustomer(salary) AREA "Customer_Table_Area";

INSERT INTO Pub.tpcustomer VALUES(800, 'cust 1', '01/06/2013', 5000);

INSERT INTO Pub.tpcustomer VALUES(850, 'cust 2', '01/06/2013', 5000);

INSERT INTO Pub.tpcustomer VALUES(900, 'cust 3', '01/06/2013', 5000);

INSERT INTO Pub.tpcustomer VALUES(1000, 'cust 4', '01/06/2013', 5000);

INSERT INTO Pub.tpcustomer VALUES(1200, 'cust 5', '01/06/2013', 5000);

INSERT INTO Pub.tpcustomer VALUES(1400, 'cust 6', '01/06/2013', 5000);

INSERT INTO Pub.tpcustomer VALUES(2000, 'cust 7', '01/06/2013', 5000);

ALTER TABLE Pub.tpcustomer

PARTITION BY RANGE custid USING TABLE AREA "Customer_Table_Area"
( PARTITION partn_2k VALUES <= (2000))

USING INDEX CustomerAlt5_idxc21, CustomerAlt5_idxc22;

At this stage table is partitioned with a single partition definition partn_2k, and all data is accessible.

The below statement add a new partition to have values less than or equal to 1000 and marks the partition as the split target:

ALTER TABLE Pub.tpcustomer
PREPARE FOR SPLIT PRO_INITIAL( PARTITION partn_1k VALUES <= (1000));

The below statement uses the PROUTIL utility to move data from the composite initial partition to target partition partn_1k.

$PROUTIL testdb -C partitionmanage split table Pub.tpcustomer composite initial useindex tpcustomer_idxc21

The above statement moves the rows satisfying condition custid<=1000 into a newly added partition. Now, the table contains one physical partition containing values <= 1000 and one composite partition contains values <= 2000 and > 1000.

The following statement splits without any partition definitions to move rest of data from the composite partition:

ALTER TABLE Pub.tpcustomer PREPARE FOR SPLIT PRO_INITIAL

The above statement marks partn_2k as the split target. Running the utility will move remaining records from the composite partition to partn_2k. In this case four records will be moved.

Migrating an unpartitioned table to a LIST-LIST partitioned table

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
( zipcode, custid, custname )
AREA "TP idx area";

CREATE INDEX sub_customer_list_list_idxlc2 ON Pub.sub_customer_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_list_idxlc1, sub_customer_list_list_idxlc2;

The below statement marks all the partitions specified above as split targets:

ALTER TABLE Pub.sub_customer_list PREPARE FOR SPLIT PRO_INITIAL

Executing the PROUTIL utility, now, moves data from the composite initial partition to the target partition.

Migrating an unpartitioned table to a LIST-RANGE partitioned table

CREATE TABLE Pub.sub_customer_range
(
Custid int,
Custname varchar (50),
Zipcode int
)
AREA "TP area";

CREATE INDEX sub_customer_range_idxlc1 ON Pub.sub_customer_range
( zipcode, custid )
AREA "TP Area";

CREATE INDEX sub_customer_range_idxgl ON Pub.sub_customer_range
(custid)
AREA "TP Area";

ALTER TABLE Pub.sub_customer_range
PARTITION BY LIST zipcode
SUBPARTITION BY RANGE custid
USING TABLE AREA "TP Area"
(
PARTITION sub_customer_range1 VALUES <= (28019, 805),
PARTITION sub_customer_range2 VALUES <= (28028, 1809),
PARTITION sub_customer_range3 VALUES <= (28039, 2809))
USING INDEX sub_customer_range_idxlc1 (ZipCode, custid);

In the above statement, the maximum value for the RANGE partition key column (custid) is specified for each leading LIST key column (zipcode)value.

The below statement further splits the sub_customer_range1 partition:

ALTER TABLE Pub.sub_customer_range
PREPARE FOR SPLIT PRO_INITIAL
(
PARTITION sub_customer_range1a VALUES <= (28019, 801),
PARTITION sub_customer_range1b VALUES <= (28019, 802),
);

The below statement marks the sub_customer_range1 partition as the split target:

ALTER TABLE Pub.sub_customer_range
PREPARE FOR SPLIT PRO_INITIAL
TARGET PARTITION sub_customer_range1;