Example - Setting a SUBPARTITION TEMPLATE v13
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)
- On this page
- Example - Resetting a SUBPARTITION TEMPLATE