Define subpartitions using subpartition templates - PolarDB

A subpartition template lets you define subpartitions once and apply that definition to every partition in a table. Without a template, you must write a separate subpartition descriptor for each partition — which becomes repetitive for tables with many partitions.

How it works

When PolarDB for Oracle creates subpartitions, it applies the following priority rules:

Explicit subpartition descriptor — If a partition has its own subpartition descriptor, that descriptor is used.
Subpartition template — If a partition has no subpartition descriptor, the subpartition template is used.
Default subpartition — If a partition has no subpartition descriptor and no template exists, a single default subpartition is created automatically.

Subpartition naming: When a subpartition template includes named subpartitions, PolarDB names each resulting subpartition as <partition_name>_<subpartition_template_name>. For example, partition parta combined with template subpartition subpartb produces the subpartition parta_subpartb.

Syntax

Create a subpartition template

General subpartition template (RANGE, LIST, or HASH 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 subpartition template — specify count only

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

Hash partitions and subpartitions — specify counts for both

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

Modify a subpartition template

Modify a general subpartition template

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

Modify a hash subpartition template — change count

ALTER TABLE table_definition SUBPARTITION TEMPLATE number;

Delete a subpartition template

ALTER TABLE table_definition SUBPARTITION TEMPLATE ();

Examples

Create a general subpartition template

The following example creates the shipments table, partitioned by order_date (RANGE) with subpartitions by delivery_date (RANGE). The SUBPARTITION TEMPLATE defines three subpartition boundaries that apply to all six partitions.

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

Each partition gets three subpartitions named using the template. For example, partition p_2006_jul produces subpartitions p_2006_jul_e, p_2006_jul_a, and p_2006_jul_l.

Create a hash subpartition template

The following example creates emp_sub_template, partitioned by deptno (RANGE) with four hash subpartitions per partition. Specifying a number in SUBPARTITION TEMPLATE sets the subpartition count — no named subpartitions are defined.

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 hash partitions and subpartitions in a single statement

When both the partitions and subpartitions use HASH, specify counts for both in the CREATE TABLE statement directly — no separate SUBPARTITION TEMPLATE clause is needed.

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

This creates four hash partitions, each with three hash subpartitions (12 subpartitions total).

Modify a general subpartition template

The following example replaces the subpartition template on hash_sub_template with four named subpartitions.

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

Modify a hash subpartition template

The following example changes the number of hash subpartitions in the template to 3.

ALTER TABLE hash_sub_template SET SUBPARTITION TEMPLATE 3;

Delete a subpartition template

The following example removes the subpartition template from hash_sub_template. After deletion, any partition without an explicit subpartition descriptor will get a single default subpartition.

ALTER TABLE hash_sub_template SUBPARTITION TEMPLATE ();