Example - Setting a SUBPARTITION TEMPLATE v13

Suggest edits

The following example creates a table sales that is range partitioned by date and hash subpartitioned by country. Use the following command to create the sales table:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE (date) SUBPARTITION BY HASH (country) SUBPARTITIONS 2
(
  PARTITION q1_2012
    VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012
    VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012
    VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012
    VALUES LESS THAN('2013-Jan-01')
);

The table definition creates four partitions (q1_2012, q2_2012, q3_2012, and q4_2012), each partition consisting of two subpartitions with system-generated names.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | Q1_2012        | SYS0101
 SALES      | Q1_2012        | SYS0102
 SALES      | Q2_2012        | SYS0103
 SALES      | Q2_2012        | SYS0104
 SALES      | Q3_2012        | SYS0105
 SALES      | Q3_2012        | SYS0106
 SALES      | Q4_2012        | SYS0107
 SALES      | Q4_2012        | SYS0108
(8 rows)

To set the subpartition template on the sales table, invoke the following command:

ALTER TABLE sales SET SUBPARTITION TEMPLATE 8;

The sales table is modified with the subpartition template set to eight. Now, if you try to add a new partition q1_2013, a new partition will be created consisting of eight subpartitions as described in the subpartition template.

ALTER TABLE sales ADD PARTITION q1_2013 VALUES LESS THAN ('2013-Apr-01');

Query the ALL_TAB_PARTITIONS view, the q1_2013 partition is successfully added comprising of eight subpartitions with system-generated names assigned to them.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' and partition_name =
'Q1_2013' ORDER BY 1,2;
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | Q1_2013        | SYS0113
 SALES      | Q1_2013        | SYS0114
 SALES      | Q1_2013        | SYS0115
 SALES      | Q1_2013        | SYS0116
 SALES      | Q1_2013        | SYS0117
 SALES      | Q1_2013        | SYS0118
 SALES      | Q1_2013        | SYS0119
 SALES      | Q1_2013        | SYS0120
(8 rows)

Example - Resetting a SUBPARTITION TEMPLATE

The following example creates a list-partitioned table sales that is list partitioned by country and hash subpartitioned by part_no. Use the following command to create the sales table:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST (country) SUBPARTITION BY HASH (part_no) SUBPARTITIONS 3
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

The table contains three partitions (americas, asia, and europe), each partition consists of three subpartitions with system-generated names.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | AMERICAS       | SYS0109
 SALES      | AMERICAS       | SYS0107
 SALES      | AMERICAS       | SYS0108
 SALES      | ASIA           | SYS0105
 SALES      | ASIA           | SYS0104
 SALES      | ASIA           | SYS0106
 SALES      | EUROPE         | SYS0101
 SALES      | EUROPE         | SYS0103
 SALES      | EUROPE         | SYS0102
(9 rows)

The following command resets the subpartition template on the sales table.

ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

The sales table is modified with the subpartition template reset to default 1. Now, try to add a new partition east_asia using the following command:

ALTER TABLE sales ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');

Query the ALL_TAB_PARTITIONS view, a new partition east_asia will be created consisting of one subpartition with a system-generated name assigned to them.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' and partition_name =
'EAST_ASIA' ORDER BY 1,2;
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | EAST_ASIA      | SYS0113
(1 row)
← Prev

ALTER TABLE...SET SUBPARTITION TEMPLATE

Next →

DROP TABLE