Example - Adding a Partition with SUBPARTITIONS num...IN PARTITION DESCRIPTION v13
The following 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 containing two subpartitions. Because the subpartitions are not 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)
The following command adds a new partition americas
to the sales
table and will create 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...
The following 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)
The following command adds a new partition americas
and five subpartitions as specified in the ADD PARTITION
clause.
ALTER TABLE sales ADD PARTITION americas VALUES ('US', 'CANADA') SUBPARTITIONS 5;
After invoking the command 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
The following 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 three partitions (americas, asia
, and europe)
, each containing 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 adds a new partition east_asia
with five subpartitions as specified in the ADD PARTITION
clause and stores them in the tablespace named (ts1)
.
ALTER TABLE sales ADD PARTITION east_asia VALUES ('CHINA', 'KOREA') SUBPARTITIONS 5 STORE IN (ts1);
After invoking the command 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)