Example: Adding a subpartition to a RANGE/LIST partitioned table v17

Suggest edits

This example adds a LIST subpartition to the RANGE partitioned sales table. 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 RANGE(date)
  SUBPARTITION BY LIST (country)
  (
    PARTITION first_half_2012 VALUES LESS THAN('01-JUL-2012')
    (
      SUBPARTITION europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION americas VALUES ('US', 'CANADA')
    ),
    PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013')
    (
      SUBPARTITION asia VALUES ('INDIA', 'PAKISTAN')
    )
);

After the command is invoked, the sales table has two partitions, named first_half_2012 and second_half_2012. The first_half_2012 partition has two subpartitions, named europe and americas. The second_half_2012 partition has one partition, named asia.

edb=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
Output
  partition_name  | subpartition_name |     high_value
------------------+-------------------+---------------------
 SECOND_HALF_2012 | ASIA              | 'INDIA', 'PAKISTAN'
 FIRST_HALF_2012  | AMERICAS          | 'US', 'CANADA'
 FIRST_HALF_2012  | EUROPE            | 'ITALY', 'FRANCE'
(3 rows)

This command adds a subpartition named east_asia to the second_half_2012 partition:

ALTER TABLE sales MODIFY PARTITION second_half_2012
  ADD SUBPARTITION east_asia VALUES ('CHINA');

After the command is invoked, the table includes a subpartition named east_asia:

edb=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
Output
  partition_name  | subpartition_name |     high_value
------------------+-------------------+---------------------
 SECOND_HALF_2012 | ASIA              | 'INDIA', 'PAKISTAN'
 SECOND_HALF_2012 | EAST_ASIA         | 'CHINA'
 FIRST_HALF_2012  | AMERICAS          | 'US', 'CANADA'
 FIRST_HALF_2012  | EUROPE            | 'ITALY', 'FRANCE'
(4 rows)
← Prev

Example: Adding a subpartition to a LIST/RANGE partitioned table

↑ Up

ALTER TABLE...ADD SUBPARTITION

Next →

ALTER TABLE...SPLIT PARTITION