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.

Table Partitioning in Oracle

In this section I’ll be discussing about the some types of partitioning options available in Oracle , its advantages and also provide some examples.

Why Partitioning?

Oracle partitioning is mainly used for manageability, availability and performance of oracle tables. Partitioning allows tables, indexes, materialized views and Index-organized tables to be further divided in to smaller manageable pieces. Partitioning enables the database objects to be managed and accessed at a finer level of granularity.

Partitioning for manageability

The partitioning option allows indexes and tables to be partitioned in to smaller manageable units. Using partition tables, DBA’s can perform maintenance on certain partitions while the rest of the partitions are still accessed by the applications.

A typical usage of partitioning for manageability is tos upport a “rolling window” load process in a data warehouse. Imagine you have to load a table with data on a monthly basis. You can take advantage of the range partition option so that each partition contains a months worth of data.
If you have to purge 6 month old data from a table on a monthly basis. The range partitioning offers a better solution. You can just delete a partition rather than issuing a DELETE command which creates additional load on the database.

Partitioning for Performance

By limiting the amount of data to be examined or operated on and by enabling parallel execution, the Oracle Partitioning option provides a number of performance benefits.

Partition Pruning
Partition pruning is the simplest and also the most substantial means to improve performance. Partition pruning can often improve query performance by several orders of magnitude.
Imagine a Orders table containing historical records of orders and the table data is partitioned by week. A query requesting data for a single week would only access a single partition of the orders table, thus by improving the performance by a bigger magnitude. Partition pruning works with all of Oracle’s other performance features. Oracle will utilize partition pruning in Conjunction with any indexing technique, join technique or parallel access method.

Partition Wise Joins

Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise join. Partition-wise join can be applied with two tables being joined together and both of these tables are partitioned on the join key. Partition-wise joins breaks a large join in to smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

Parallel Execution

Partitioning enables parallel execution of UPDATE, DELETE and MERGE statements. Oracle will parallelize SELECT statements and INSERT statements when accessing both partitioned and non-partitioned database objects. UPDATE, DELETE and MERGE statements can be parallelized for both partitioned and non-partitioned database objects when no bit map indexes are present. In order to parallelize the operations on objects having bit map indexes , the target table must be partitioned. Parallel execution of sql statements can vastly improve performance, particularly for UPADTE, DELETE or MERGE operations involving large volumes data.

Partitioning for Availability

The DBA can store different partitions in different tablespaces which would allow him/her to perform backup/recovery operations on each individual partition, independent of the other partitions in the table.

Partitioned database objects provide partition independence. If any one of the partitions become unavailable, all other partitions of the table remain online and available. Applications can still use the available partitions while the DBA can work on fixing the failed partition/partitions.


Types of Partitioning

· Range Partitioning
· Hash Partitioning
· List Partitioning
· Composite partitioning

In the next post, I’ll be discussing the above-mentioned partitioning options in detail.