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)