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