PolarDB:Subpartition templates

Overview

PolarDB for PostgreSQL (Compatible with Oracle) allows you to use a subpartition template to create subpartitions in a composite partitioned table. A subpartition template simplifies the specification of subpartitions within a table. Instead of specifying a subpartition descriptor for each individual partition, you can define the subpartitioning scheme once in a subpartition template and apply the template to all partitions. For an interval-* composite partitioned table, you can only use a subpartition template to define the subpartitions for the interval partitions.

• If you do not specify a subpartition descriptor for a partition, the system automatically uses the subpartition template to define the subpartition.

• If you specify a subpartition descriptor for a partition, the system automatically uses the descriptor to define the subpartition.

• If you do not specify a subpartition descriptor for a partition or no subpartition template is available, the system automatically creates a default subpartition.

• If you specify subpartition names in a subpartition template, subpartitions that are created based on this template are named in the partition name_subpartition template format. For example, if the partition name is parta and the subpartition template name is subpartb, the subpartition name is parta_subpartb.

Syntax

Use a subpartition template to create subpartitions

------General syntax.
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]...);

------Syntax applicable if you are to use a subpartition template to structure hash subpartitions in a table.
CREATE TABLE table_definition PARTITION BY hash/range/list (column[, column ]...) 
SUBPARTITION BY hash(column[, column ]...) 
SUBPARTITION TEMPLATE num
(table_partition[, table_partition]...);

------Syntax applicable if you are to use a subpartition template to structure hash subpartitions across hash partitions in a table.
CREATE TABLE table_definition PARTITION BY hash (column[, column ]...) 
SUBPARTITION BY hash(column[, column ]...)  PARTITIONS num SUBPARTITIONS num;

Modify a subpartition template

------General syntax.
ALTER TABLE table_definition SUBPARTITION TEMPLATE (subpartition[, subpartition], ...);

------Syntax applicable if you are to modify a subpartition template that is used to structure hash subpartitions in a table.
ALTER TABLE table_definition SUBPARTITION TEMPLATE number;

Delete a subpartition template

ALTER TABLE table_definition SUBPARTITION TEMPLATE ();

Examples

Use a subpartition template to structure range subpartitions in a table

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

Use a subpartition template to structure hash subpartitions in a table

-----Syntax applicable if you are to use a subpartition template to structure hash subpartitions in a table.
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)
    );

------Syntax applicable if you are to use a subpartition template to structure hash subpartitions across hash partitions in a table.
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 subpartition template

------General syntax.
ALTER TABLE hash_sub_template SET SUBPARTITION TEMPLATE
(SUBPARTITION a1,
          SUBPARTITION b1,
          SUBPARTITION c1,
          SUBPARTITION d1);

------Syntax applicable if you are to modify a subpartition template that is used to structure hash subpartitions in a table.
ALTER TABLE hash_sub_template SET SUBPARTITION TEMPLATE 3;

View subpartition template information

You can view the subpartition template information of a partitioned table in the BA_SUBPARTITION_TEMPLATES system view.

Example:

SELECT * FROM DBA_SUBPARTITION_TEMPLATES;
 user_name |    table_name     | subpartition_name | subpartition_position | tablespace_name | high_bound 
-----------+-------------------+-------------------+-----------------------+-----------------+------------
 PolarDB   | EMP_SUB_TEMPLATE  |                   |                     1 | PG_DEFAULT      | 
 PolarDB   | EMP_SUB_TEMPLATE  |                   |                     2 | PG_DEFAULT      | 
 PolarDB   | EMP_SUB_TEMPLATE  |                   |                     3 | PG_DEFAULT      | 
 PolarDB   | EMP_SUB_TEMPLATE  |                   |                     4 | PG_DEFAULT      | 
 PolarDB   | HASH_SUB_TEMPLATE | A1                |                     1 | PG_DEFAULT      | 
 PolarDB   | HASH_SUB_TEMPLATE | B1                |                     2 | PG_DEFAULT      | 
 PolarDB   | HASH_SUB_TEMPLATE | C1                |                     3 | PG_DEFAULT      | 
 PolarDB   | HASH_SUB_TEMPLATE | D1                |                     4 | PG_DEFAULT      | 
(8 rows)