Define Subpartition Templates for Composite Partitioned Tables - PolarDB

Defining subpartitions individually for each partition in a composite partitioned table is repetitive and error-prone. A subpartition template lets you define the subpartitioning scheme once and apply it to every partition automatically — including interval partitions, which have no other way to receive a subpartition definition.

How it works

When PolarDB for PostgreSQL (Compatible with Oracle) creates subpartitions, it applies the following precedence:

Condition	Behavior
The partition has its own subpartition descriptor	The system uses that descriptor
The partition has no descriptor, and a template exists	The system uses the subpartition template
The partition has no descriptor and no template exists	The system creates a default subpartition

Subpartition naming: When a template defines named subpartitions, the generated names follow the format partition_name_subpartition_template_name. For example, partition parta with template subpartition subpartb produces the subpartition name parta_subpartb.

Important

For interval-* composite partitioned tables, a subpartition template is the only way to define subpartitions for interval partitions. Without a template, the system creates a default subpartition for each interval partition.

Syntax

Create a subpartition template

Range or list subpartitions:

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

Hash subpartitions using a numeric count:

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

Hash-in-hash shorthand (partition and subpartition counts in one clause):

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

Modify a subpartition template

Replace with named subpartitions:

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

Update a numeric hash template:

ALTER TABLE table_definition SUBPARTITION TEMPLATE number;

Delete a subpartition template

ALTER TABLE table_definition SUBPARTITION TEMPLATE ();

Examples

Use a template for range subpartitions

The following example creates a shipments table partitioned by order_date (range), with range subpartitions on delivery_date defined by a template.

The template defines three named subpartitions — e, a, and l — which are automatically applied to each of the six monthly partitions. This produces:

18 subpartitions in total (6 partitions x 3 template subpartitions each)
Subpartition names in partition_name_subpartition_template_name format — for example, p_2006_jul_e, p_2006_jul_a, and p_2006_jul_l

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

To verify the subpartition template definition after creating the table, query DBA_SUBPARTITION_TEMPLATES:

SELECT * FROM DBA_SUBPARTITION_TEMPLATES WHERE TABLE_NAME = 'SHIPMENTS';

Use a template for hash subpartitions

Numeric template — fixed number of hash subpartitions per partition:

The following example creates emp_sub_template, partitioned by deptno (range) with hash subpartitions on empname. The numeric template 4 means each partition gets exactly four hash subpartitions.

This produces 12 subpartitions in total (3 partitions x 4 hash subpartitions each). The subpartitions are unnamed and system-assigned.

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

Hash-in-hash shorthand — define both partition and subpartition counts in one clause:

The following example creates hash_sub_template with 4 hash partitions and 3 hash subpartitions per partition, producing 12 subpartitions in total.

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

Replace with named subpartitions:

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

Update the numeric hash template:

ALTER TABLE hash_sub_template SET SUBPARTITION TEMPLATE 3;

View subpartition template information

Query DBA_SUBPARTITION_TEMPLATES to inspect the subpartition template defined for a partitioned table.

Column name	Description
USER_NAME	The username.
TABLE_NAME	The name of the table.
SUBPARTITION_NAME	The name of the subpartition in the template.
SUBPARTITION_POSITION	The position of the subpartition in the template, starting from 1.
TABLESPACE_NAME	The name of the tablespace.
HIGH_BOUND	The upper boundary of the subpartition.

Run the following query to view all subpartition templates for the current user:

SELECT * FROM DBA_SUBPARTITION_TEMPLATES;

Example output, reflecting the emp_sub_template table (4 unnamed hash subpartitions) and the hash_sub_template table (4 named subpartitions after the ALTER TABLE above):

 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)