Partition PROMOTE Statement

Description

Partition PROMOTE statement promotes the operator-specified partition on the partition table to a general standalone table. This is useful to retain the old data, which is rarely accessed, to archive only. By promoting the partition to a general table, useful data has less partitions, reducing the access load and archiving the old data in a convenient manner.

The partition PROMOTE statement is allowed for the range partition table and the list partition table only. Promotion of the hash partition table is not allowed since it cannot be controlled by an operator.

When the partition is promoted to a standalone table, the table inherits the data and local indexes only. It means that the following table attributes are not saved in the promotion table.

• Primary Key
• Foreign key
• Unique index
• AUTO_INCREMENT attribute and serial
• Triggers
• Methods
• Inheritance relationship (super-class and sub-class)

The following attributes are used as they are on the promoted table:

• Record attributes (column types)
• Table attributes
• Local indexes (general indexes, not the unique indexes and primary keys)

Constraints

• If the partition table includes any foreign key, the partition cannot be promoted.
• Promoting the hash partition table is not allowed.

Syntax

ALTER TABLE identifier PROMOTE PARTITION <identifier_list>

• <identifier_list>: The name of a partition to promote

Example

The following example shows promotion of list partition:

CREATE TABLE t(i int) PARTITION BY LIST(i) (

    partition p0 values in (1, 2, 3),

    partition p1 values in (4, 5, 6),

    partition p2 values in (7, 8, 9),

    partition p3 values in (10, 11, 12)

);

ALTER TABLE t PROMOTE PARTITION p1, p2;

After promotion, the partition of the t table has p0 and p3 only and p1 and p2 can be accessed through the t__p__p1 table and the t__p__p2 table, respectively.

csql> ;schema t

=== <Help: Schema of a Class> ===

 <Class Name>

     t

 <Sub Classes>

     t__p__p0

     t__p__p3

 <Attributes>

     i                    INTEGER

 <Partitions>

     PARTITION BY LIST ([i])

     PARTITION p0 VALUES IN (1, 2, 3)

     PARTITION p3 VALUES IN (10, 11, 12)

csql> ;schema t__p__p1

=== <Help: Schema of a Class> ===

 <Class Name>

     t__p__p1

 <Attributes>

     i                    INTEGER

The following example shows promotion of range partition.

CREATE TABLE t(i int, j int) PARTITION BY RANGE(i) (

        PARTITION p0 VALUES LESS THAN (1),

        PARTITION p1 VALUES LESS THAN (10),

        PARTITION p2 VALUES LESS THAN (100),

        PARTITION p3 VALUES LESS THAN MAXVALUE

      );

CREATE UNIQUE INDEX u_t_i ON t(i);

CREATE INDEX i_t_j ON t(j);

ALTER TABLE t PROMOTE PARTITION p1, p2;

After promotion, the partition of the t table has p0 and p3 only and p1 and p2 can be accessed through the t__p__p1 table and the t__p__p2 table, respectively. Note that some attributes or indexes such as the primary keys, foreign keys, and unique keys have been removed from t__p__p1 and t__p__p2, the promoted tables.

csql> ;schema t

=== <Help: Schema of a Class> ===

 <Class Name>

     t

 <Sub Classes>

     t__p__p0

     t__p__p3

 <Attributes>

     i                    INTEGER

     j                    INTEGER

 <Constraints>

    UNIQUE u_t_i ON t (i)

    INDEX i_t_j ON t (j)

 <Partitions>

     PARTITION BY RANGE ([i])

     PARTITION p0 VALUES LESS THAN (1)

     PARTITION p3 VALUES LESS THAN MAXVALUE

csql> ;schema t__p__p1

=== <Help: Schema of a Class> ===

 <Class Name>

     t__p__p1

 <Attributes>

     i                    INTEGER

     j                    INTEGER

 <Constraints>

    INDEX idx_t_j ON t (j)