Partitioning - Maintenance

Add a Partition.

Adding a partition has different side affects for different partition types. For range partitions, a new partition is added at the end mainly to specify a new high-end value. You cannot add a partition if MAXVALUE partition exists. Adding a partition does not not mark a global index unusable. For hash partition or hash subpartition in composite partition, adding a partition will receive rows redistributed from other partitions.

Example

Alter table sales_range
Add partition sales_mar2009 values Less
Than (to_date(‘03/01/2009’,’dd/mm/yyyy’))
Tablespace tsp1;

Alter table q1_sales_by_region
Add partition q1_outerregion values (‘HI’,’PR’)
Tablespace tbsp2;

Drop and Truncate Partition

Dropping a partition will discard the rows quickly, uses few system resources and doesn’t use rollback. Only range and list partitions can be dropped. If a table contains only one partition the partition cannot be dropped. You must drop the table. If range partition, if you want to remove the range key but keep the data then you should merge the partitions and not drop the partition. Only one partition can be dropped at a time. You can truncate a partition to discard the data rows in the partition but not remove the partition. The corresponding local indexes are also truncated. If you truncate the table then it will discard rows from all partitions.

Example

Alter table sales drop partition jan2000;

Alter table sales truncate partition jan2000;

Split, Merge and Coalesce Partition

Splitting a partition will create two new partitions filled with rows of the split partition. Merging a partition collects the rows from two partitions and drops them into one partition. Hash partitions cannot be split or merged. Coalesce is used on hash partitions. It is same as merge on non-hash partitioned tables. Coalescing is also used to reorganize a partition of an IOT table.


Example

Alter table sales_list
Split partition sales_central values (‘texas’) into partition sales_south, sales_southwest;

Alter table sales_range
Split partition sales_jan2009 values less
Than (to_date(‘01/16/2009’,’dd/mm/yyyy’))
Into partition sales_jan2009_1,sales_jan2009_2;

Alter table sales_range
Merge partition sales_jan2009, sales_feb2009 into partition sales_feb2009;

Alter table sales_list
Merge partition sales_east,sales_central into partition sales_central;

Alter table sales_hash coalesce partition;

Move and Rename Partition

Moving a partition is generally used to replace a partition in a new tablespace. In order to move a partitioned table you will have to move all the partitions. Global indexes are marked unusable unless there is no data in the partition that is moved or update global indexes command is used. Renaming a partition is to change name of the partition. There are no restrictions on renaming a partition name as long as the partition name is unique with in the partitioned table or index.

Example

Alter table sales_list move partition sales_east tablespace sales_new;

Alter table sales_list rename partition sales_west to sales_west_north;

Exchange Partition

Exchange partition is to swap names. You can exchange a partition with a non-partitioned table. This operation does not move rows. The non-partitioned table must have the same structure as the partitioned table.

Example

Alter table sales_list
Exchange partition sales_west with sales_west_temp;



DBMS_REDEFINITION package can be used to take a non-partitioned tables and change it to partitioned tables when its is being accessed.