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.
The sales table contains partition q1_2012 and the three subpartitions europe, americas, and asia:
This command splits the q1_2012 partition into partitions named q1_2012_p1 and 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:
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:
The table definition creates the three partitions europe, asia, and americas. Each contains two subpartitions.
This command splits the americas partition into partitions named partition_us and 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.
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:
The sales table contains the partitions europe, asia, and americas. Each contains four subpartitions with system-generated names.
This command splits the europe partition into the partitions france and 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.