Example - Splitting a RANGE/LIST Partition v13

The following example will divide one of the partitions in the range-partitioned sales table into new partitions, maintaining the partitioning of the original table in the newly created partitions, and redistributing the contents of the partition between them.

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 q1_2012 VALUES LESS THAN('2012-Apr-01')
    (
      SUBPARTITION europe VALUES('FRANCE', 'ITALY'),
      SUBPARTITION americas VALUES('US', 'CANADA'),
      SUBPARTITION asia VALUES('INDIA', 'PAKISTAN')
    )
  );

The sales table contains partition q1_2012 and three subpartitions europe, americas, and asia.

edb=# SELECT table_name, partition_name, subpartition_name, high_value FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
 table_name | partition_name | subpartition_name |     high_value
------------+----------------+-------------------+---------------------
 SALES      | Q1_2012        | EUROPE            | 'FRANCE', 'ITALY'
 SALES      | Q1_2012        | AMERICAS          | 'US', 'CANADA'
 SALES      | Q1_2012        | ASIA              | 'INDIA', 'PAKISTAN'
(3 rows)

The following command splits the q1_2012 partition into two partitions named q1_2012_p1 and q1_2012_p2.

ALTER TABLE sales SPLIT PARTITION q1_2012
  AT ('01-Mar-2012')
  INTO
  (
    PARTITION q1_2012_p1,
    PARTITION q1_2012_p2
  );

A SELECT statement confirms that the same number of subpartitions is created in the newly created partitions q1_2012_p1 and q1_2012_p2 with system-generated names.

edb=# SELECT table_name, partition_name, subpartition_name, high_value FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2,3;
 table_name | partition_name | subpartition_name |     high_value
------------+----------------+-------------------+---------------------
 SALES      | Q1_2012_P1     | SYS0105           | 'US', 'CANADA'
 SALES      | Q1_2012_P1     | SYS0106           | 'FRANCE', 'ITALY'
 SALES      | Q1_2012_P1     | SYS0107           | 'INDIA', 'PAKISTAN'
 SALES      | Q1_2012_P2     | SYS0108           | 'US', 'CANADA'
 SALES      | Q1_2012_P2     | SYS0109           | 'FRANCE', 'ITALY'
 SALES      | Q1_2012_P2     | SYS0110           | 'INDIA', 'PAKISTAN'
(6 rows)

Example - Splitting a Partition with SUBPARTITIONS num...

The following example will divide one of the partitions in the list-partitioned sales table into new partitions, maintaining the partitioning of the original table in the newly created partitions, and redistributing the contents of the partition between them. The SUBPARTITIONS clause lets you add a specified number of subpartitions. If no SUBPARTITIONS clause is specified then the new partitions inherit the default number of subpartitions. The sales table is created with the statement:

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'),
  PARTITION americas VALUES('US', 'CANADA')
);

The table definition creates three partitions (europe, asia, and americas) each containing two 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       | SYS0105
 SALES      | AMERICAS       | SYS0106
 SALES      | ASIA           | SYS0103
 SALES      | ASIA           | SYS0104
 SALES      | EUROPE         | SYS0101
 SALES      | EUROPE         | SYS0102
(6 rows)

The following command splits the americas partition into two partitions named partition_us and partition_canada.

ALTER TABLE sales SPLIT PARTITION americas VALUES ('US') INTO (PARTITION
partition_us SUBPARTITIONS 5, PARTITION partition_canada);

A SELECT statement confirms that the americas partition is split into partition_us and partition_canada. The partition_us contains five subpartitions and partition_canada contains default two subpartitions and assigned system-generated names.

edb=#  SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2,3;
 table_name |  partition_name  | subpartition_name
------------+------------------+-------------------
 SALES      | ASIA             | SYS0103
 SALES      | ASIA             | SYS0104
 SALES      | EUROPE           | SYS0101
 SALES      | EUROPE           | SYS0102
 SALES      | PARTITION_CANADA | SYS0115
 SALES      | PARTITION_CANADA | SYS0116
 SALES      | PARTITION_US     | SYS0110
 SALES      | PARTITION_US     | SYS0111
 SALES      | PARTITION_US     | SYS0112
 SALES      | PARTITION_US     | SYS0113
 SALES      | PARTITION_US     | SYS0114
(11 rows)

Example - Splitting a Partition with SUBPARTITIONS num...STORE IN

The following example will divide the europe partition of the list-partitioned sales table into two partitions, maintaining the partitioning of the original table in the newly created partitions, and redistributing the partition's contents. The SUBPARTITIONS clause lets you add a specified number of subpartitions. Use the following command to create the sales table:

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 4
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

The sales table contains three partitions (europe, asia, and americas) each containing four subpartitions with system-generated names 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      | AMERICAS       | SYS0112
 SALES      | AMERICAS       | SYS0111
 SALES      | AMERICAS       | SYS0109
 SALES      | AMERICAS       | SYS0110
 SALES      | ASIA           | SYS0107
 SALES      | ASIA           | SYS0105
 SALES      | ASIA           | SYS0106
 SALES      | ASIA           | SYS0108
 SALES      | EUROPE         | SYS0101
 SALES      | EUROPE         | SYS0104
 SALES      | EUROPE         | SYS0103
 SALES      | EUROPE         | SYS0102
(12 rows)

The following command splits the europe partition into two partitions named france and italy.

ALTER TABLE sales SPLIT PARTITION europe VALUES ('FRANCE') INTO (PARTITION
france SUBPARTITIONS 10 STORE IN (ts1), PARTITION italy);

A SELECT statement confirms that the europe partition is split into two partitions. The partition france contains ten subpartitions that are stored in the tablespace ts1 and partition italy contains four subpartitions as in the original partition europe.

edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name
FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2,3;
 table_name | partition_name | subpartition_name | tablespace_name
------------+----------------+-------------------+-----------------
 SALES      | AMERICAS       | SYS0109           |
 SALES      | AMERICAS       | SYS0110           |
 SALES      | AMERICAS       | SYS0111           |
 SALES      | AMERICAS       | SYS0112           |
 SALES      | ASIA           | SYS0105           |
 SALES      | ASIA           | SYS0106           |
 SALES      | ASIA           | SYS0107           |
 SALES      | ASIA           | SYS0108           |
 SALES      | FRANCE         | SYS0116           | TS1
 SALES      | FRANCE         | SYS0117           | TS1
 SALES      | FRANCE         | SYS0118           | TS1
 SALES      | FRANCE         | SYS0119           | TS1
 SALES      | FRANCE         | SYS0120           | TS1
 SALES      | FRANCE         | SYS0121           | TS1
 SALES      | FRANCE         | SYS0122           | TS1
 SALES      | FRANCE         | SYS0123           | TS1
 SALES      | FRANCE         | SYS0124           | TS1
 SALES      | FRANCE         | SYS0125           | TS1
 SALES      | ITALY          | SYS0126           |
 SALES      | ITALY          | SYS0127           |
 SALES      | ITALY          | SYS0128           |
 SALES      | ITALY          | SYS0129           |
(22 rows)