Example: Adding a partition with SUBPARTITIONS num...IN PARTITION DESCRIPTION v17
This example adds a partition to a list-partitioned sales table. You can specify a SUBPARTITIONS clause to add a specified number of subpartitions. The sales table was created with the command:
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 2 ( PARTITION europe VALUES ('FRANCE', 'ITALY'), PARTITION asia VALUES ('INDIA', 'PAKISTAN') );
The table contains two partitions: europe and asia. Each contains two subpartitions. Because the subpartitions aren't named, system-generated names are assigned to them.
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 | ASIA | SYS0103 SALES | ASIA | SYS0104 SALES | EUROPE | SYS0101 SALES | EUROPE | SYS0102 (4 rows)
This command adds a partition americas to the sales table and creates a number of subpartitions as specified in the partition description:
ALTER TABLE sales ADD PARTITION americas VALUES ('US', 'CANADA');
After invoking the command, the table includes the partition americas and two newly added subpartitions:
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 | SYS0107 SALES | AMERICAS | SYS0108 SALES | ASIA | SYS0103 SALES | ASIA | SYS0104 SALES | EUROPE | SYS0101 SALES | EUROPE | SYS0102 (6 rows)
Example - Adding a Partition with SUBPARTITIONS num...
This example adds a partition a list-partitioned table sales consisting of three subpartitions. The sales table was created with the command:
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') );
The table contains partitions europe and asia, each containing three subpartitions:
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 | ASIA | SYS0104 SALES | ASIA | SYS0105 SALES | ASIA | SYS0106 SALES | EUROPE | SYS0101 SALES | EUROPE | SYS0102 SALES | EUROPE | SYS0103 (6 rows)
This command adds a partition americas and five subpartitions, as specified in the ADD PARTITION clause:
ALTER TABLE sales ADD PARTITION americas VALUES ('US', 'CANADA') SUBPARTITIONS 5;
After the command is invoked, the sales table includes the partition americas and five newly added subpartitions:
edb=# SELECT table_name, partition_name, subpartition_name FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' and partition_name = 'AMERICAS' ORDER BY 1,2;
table_name | partition_name | subpartition_name ------------+----------------+------------------- SALES | AMERICAS | SYS0109 SALES | AMERICAS | SYS0110 SALES | AMERICAS | SYS0111 SALES | AMERICAS | SYS0112 SALES | AMERICAS | SYS0113 (5 rows)
Example: Adding a partition with SUBPARTITIONS num... STORE IN
This example adds a partition to a list-partitioned table sales consisting of three subpartitions. The table was created using the command:
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 the three partitions americas, asia, and europe. Each contains 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)
This command adds a partition east_asia with five subpartitions as specified in the ADD PARTITION clause. It stores them in the tablespace named ts1.
ALTER TABLE sales ADD PARTITION east_asia VALUES ('CHINA', 'KOREA') SUBPARTITIONS 5 STORE IN (ts1);
After the command is invoked, the table includes the partition east_asia and five newly added subpartitions stored in tablespace ts1:
edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' and partition_name = 'EAST_ASIA' ORDER BY 1,2;
table_name | partition_name | subpartition_name | tablespace_name ------------+----------------+-------------------+----------------- SALES | EAST_ASIA | SYS0113 | TS1 SALES | EAST_ASIA | SYS0114 | TS1 SALES | EAST_ASIA | SYS0115 | TS1 SALES | EAST_ASIA | SYS0116 | TS1 SALES | EAST_ASIA | SYS0117 | TS1 (5 rows)