Example: Splitting a RANGE/LIST partition v17
This example divides one of the partitions in the range-partitioned sales table into new partitions. This approach maintains the partitioning of the original table in the newly created partitions and redistributes 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 the 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)
This command splits the q1_2012 partition into 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...
This example divides one of the partitions in the list-partitioned sales table into new partitions. It maintains the partitioning of the original table in the newly created partitions and redistributes the contents of the partition between them. The SUBPARTITIONS clause lets you add a specified number of subpartitions. Without the SUBPARTITIONS clause, the new partitions inherit the default number of subpartitions.
This statement creates 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 2 ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') );
The table definition creates the three partitions europe, asia, and americas. Each contains 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)
This command splits the americas partition into 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. partition_canada contains two default 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,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
This example divides the europe partition of the list-partitioned sales table into two partitions. It maintains the partitioning of the original table in the newly created partitions and redistributes 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 the partitions europe, asia, and americas. Each contains four 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;
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)
This command splits the europe partition into the partitions 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 10 subpartitions that are stored in the tablespace ts1. 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)