Subpartition templates - PolarDB - Alibaba Cloud Documentation Center

You can use subpartition templates to define subpartitions and simplify the specification of subpartitions. You need only to define the subpartition descriptor in a subpartition template and then apply the subpartition template to each partition in a table. This way, you can define the subpartition descriptors for multiple partitions in the table at a time.

Notes

If you do not specify a subpartition descriptor for a partition, the system automatically uses a subpartition template to define subpartitions.
If you have specified a subpartition descriptor for a partition, the system automatically uses the descriptor to define subpartitions.
If you do not specify a subpartition descriptor for a partition and no subpartition template is available, the system automatically creates a default subpartition.
If you have specified a subpartition name in a subpartition template, subpartitions that are created based on this template are named in the "partition name_subpartition template name" format. For example, if the partition name is parta and the subpartition template name is subpartb, the subpartition name is parta_subpartb.

Syntax

Create a general subpartition template.

CREATE TABLE  table_definition PARTITION BY hash/range/list (column[, column ]...)
SUBPARTITION BY hash/range/list (column[, column ]...)
SUBPARTITION TEMPLATE (subpartition[, subpartition], ...)
(table_partition[, table_partition]...);

Create a subpartition template that is applicable to hash subpartitions.

CREATE TABLE  table_definition PARTITION BY hash/range/list (column[, column ]...)
SUBPARTITION BY hash(column[, column ]...)
SUBPARTITION TEMPLATE num
(table_partition[, table_partition]...);

Create a subpartition template that is applicable to hash partitions and subpartitions.

CREATE TABLE  table_definition PARTITION BY hash (column[, column ]...)
SUBPARTITION BY hash(column[, column ]...)  PARTITIONS num SUBPARTITIONS num;

Modify a general subpartition template.

ALTER TABLE  table_definition SUBPARTITION TEMPLATE (subpartition[, subpartition], ...)

Modify a subpartition template that is applicable to hash subpartitions.
```
ALTER TABLE  table_definition SUBPARTITION TEMPLATE number
```

Delete a subpartition template.

ALTER TABLE  table_definition SUBPARTITION TEMPLATE ()

Examples

Create a general subpartition template.

CREATE TABLE shipments
( order_id      NUMBER NOT NULL,
  order_date    DATE NOT NULL,
  delivery_date DATE NOT NULL,
  customer_id   NUMBER NOT NULL,
  sales_amount  NUMBER NOT NULL
)
PARTITION BY RANGE (order_date) SUBPARTITION BY RANGE(delivery_date)
     SUBPARTITION TEMPLATE
   (SUBPARTITION e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy')),
    SUBPARTITION a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy')),
    SUBPARTITION l VALUES LESS THAN (MAXVALUE) )
   ( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy')),
  PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy')),
  PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')),
  PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy')),
  PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy')),
  PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);

Create a subpartition template that is applicable to hash subpartitions.

CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
     SUBPARTITION TEMPLATE  4
    (PARTITION p1 VALUES LESS THAN (1000),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (6000)
    );

Create a subpartition template that is applicable to hash partitions and subpartitions.

CREATE TABLE hash_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
     PARTITION BY HASH(deptno) SUBPARTITION BY HASH(empname) PARTITIONS 4 SUBPARTITIONS 3;

Modify a general subpartition template.

ALTER TABLE  hash_sub_template SET SUBPARTITION TEMPLATE
(SUBPARTITION a1,
          SUBPARTITION b1,
          SUBPARTITION c1,
          SUBPARTITION d1);

Modify a subpartition template that is applicable to hash subpartitions.
```
ALTER TABLE  hash_sub_template SET SUBPARTITION TEMPLATE 3;
```