Example: Splitting a lIST subpartition v17

Suggest edits

This example splits a list subpartition, redistributing the subpartition's contents between two new subpartitions.

The sample table sales 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 p1_europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION p1_americas VALUES ('US', 'CANADA')
    ),
    PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013')
    (
      SUBPARTITION p2_europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION p2_americas VALUES ('US', 'CANADA')
    )
  );

The sales table has partitions named first_half_2012 and second_half_2012. Each partition has two range-defined subpartitions that distribute the partition's contents into subpartitions based on the value of the country column.

edb=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
Output
  partition_name  | subpartition_name |    high_value
------------------+-------------------+-------------------
 SECOND_HALF_2012 | P2_AMERICAS       | 'US', 'CANADA'
 SECOND_HALF_2012 | P2_EUROPE         | 'ITALY', 'FRANCE'
 FIRST_HALF_2012  | P1_AMERICAS       | 'US', 'CANADA'
 FIRST_HALF_2012  | P1_EUROPE         | 'ITALY', 'FRANCE'
(4 rows)

This command adds rows to each subpartition:

INSERT INTO sales VALUES
  (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
  (40, '9519b', 'US', '12-Apr-2012', '145000'),
  (40, '4577b', 'US', '11-Nov-2012', '25000'),
  (30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
  (30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
  (30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
  (40, '3788a', 'US', '12-May-2012', '4950'),
  (10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
  (10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
  (10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
  (40, '4788a', 'US', '23-Sept-2012', '4950'),
  (40, '4788b', 'US', '09-Oct-2012', '15000');

A SELECT statement confirms that the rows are correctly distributed among the subpartitions:

edb=# SELECT tableoid::regclass, * FROM sales;
Output
   tableoid      | dept_no | part_no | country |        date        | amount
-----------------+---------+---------+---------+--------------------+--------
sales_p1_americas|      40 | 9519b   | US      | 12-APR-12 00:00:00 | 145000
sales_p1_americas|      30 | 9519b   | CANADA  | 01-FEB-12 00:00:00 |  75000
sales_p1_americas|      30 | 4519b   | CANADA  | 08-APR-12 00:00:00 | 120000
sales_p1_americas|      40 | 3788a   | US      | 12-MAY-12 00:00:00 |   4950
sales_p1_europe  |      10 | 4519b   | FRANCE  | 17-JAN-12 00:00:00 |  45000
sales_p2_americas|      40 | 4577b   | US      | 11-NOV-12 00:00:00 |  25000
sales_p2_americas|      30 | 7588b   | CANADA  | 14-DEC-12 00:00:00 |  50000
sales_p2_americas|      40 | 4788a   | US      | 23-SEP-12 00:00:00 |   4950
sales_p2_americas|      40 | 4788b   | US      | 09-OCT-12 00:00:00 |  15000
sales_p2_europe  |      10 | 9519b   | ITALY   | 07-JUL-12 00:00:00 |  15000
sales_p2_europe  |      10 | 9519a   | FRANCE  | 18-AUG-12 00:00:00 | 650000
sales_p2_europe  |      10 | 9519b   | FRANCE  | 18-AUG-12 00:00:00 | 650000
(12 rows)

This command splits the p2_americas subpartition into two new subpartitions and redistributes the contents.

ALTER TABLE sales SPLIT SUBPARTITION p2_americas
  VALUES ('US')
  INTO
  (
    SUBPARTITION p2_us,
    SUBPARTITION p2_canada
  );

After the command is invoked, the p2_americas subpartition is deleted. In its place, the server creates the subpartitions p2_us and p2_canada:

edb=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
Output
  partition_name  | subpartition_name |    high_value
------------------+-------------------+-------------------
 FIRST_HALF_2012  | P1_EUROPE         | 'ITALY', 'FRANCE'
 FIRST_HALF_2012  | P1_AMERICAS       | 'US', 'CANADA'
 SECOND_HALF_2012 | P2_EUROPE         | 'ITALY', 'FRANCE'
 SECOND_HALF_2012 | P2_US             | 'US'
 SECOND_HALF_2012 | P2_CANADA         | 'CANADA'
(5 rows)

Querying the sales table shows that the content of the p2_americas subpartition was redistributed:

edb=# SELECT tableoid::regclass, * FROM sales;
Output
   tableoid       | dept_no | part_no | country |        date        |amount
------------------+---------+---------+---------+--------------------+------
 sales_p1_americas|      40 | 9519b   | US      | 12-APR-12 00:00:00 |145000
 sales_p1_americas|      30 | 9519b   | CANADA  | 01-FEB-12 00:00:00 | 75000
 sales_p1_americas|      30 | 4519b   | CANADA  | 08-APR-12 00:00:00 |120000
 sales_p1_americas|      40 | 3788a   | US      | 12-MAY-12 00:00:00 |  4950
 sales_p1_europe  |      10 | 4519b   | FRANCE  | 17-JAN-12 00:00:00 | 45000
 sales_p2_canada  |      30 | 7588b   | CANADA  | 14-DEC-12 00:00:00 | 50000
 sales_p2_europ   |      10 | 9519b   | ITALY   | 07-JUL-12 00:00:00 | 15000
 sales_p2_europe  |      10 | 9519a   | FRANCE  | 18-AUG-12 00:00:00 |650000
 sales_p2_europe  |      10 | 9519b   | FRANCE  | 18-AUG-12 00:00:00 |650000
 sales_p2_us      |      40 | 4577b   | US      | 11-NOV-12 00:00:00 | 25000
 sales_p2_us      |      40 | 4788a   | US      | 23-SEP-12 00:00:00 |  4950
 sales_p2_us      |      40 | 4788b   | US      | 09-OCT-12 00:00:00 | 15000
(12 rows)
← Prev

ALTER TABLE...SPLIT SUBPARTITION

↑ Up

ALTER TABLE...SPLIT SUBPARTITION

Next →

Example: Splitting a RANGE subpartition