Database vendors have been advocating data partitioning techniques for managing large volumes of data over the past several years. Oracle has pioneered this trend with Oracle Database 8 and improved the features steadily over the later versions. Partitioning helps with data manageability, ready availability, speeding up back ups, and managing overall performance. Let us review some of the strategies for using table as well as index partitioning in Oracle Database 11g.
The basic idea behind partitioning is to divide a database table or index into smaller pieces or partitions using a partitioning key. Each smaller partition can have its own name and storage characteristics. The multiple pieces of a large database object give the database administrator a lot of flexibility and manageability in dealing with partitioned database objects. To the database developer or end-user, partitioning does not make any performance differences. Some of the common scenarios for table partition are when the table gets larger than a couple of gigabytes or when the table stores historical data that could be archived. For index partitioning, we use indexes where data is frequently moved from/to tables and an index column with increasing values.
One of the most widely used partitioning techniques is partition pruning. With partition pruning, the database optimizer looks at the query statement and eliminates unwanted partitions in building the partition access list. This drastically reduces the volume of data retrieved from disk and improves processing time and query performance. For a table with index and table partitioning on different columns, partition pruning eliminates index partitions even when table partitions are used in the query. There are two types of pruning–static or dynamic pruning. While static pruning occurs at query compiling time of queries, dynamic pruning occurs at the use of database operators or functions in the WHERE condition of the query clause.
We can use range or interval partitioning for database tables and indexes. Range partitioning is an older and easy way of partitioning historical data by defining the ordering of the partitions in the tables or indexes. We extend range partitioning to interval partitioning where beyond a point in time, partitions are defined by an interval. The advantage of interval partitions is that partitioned are automatically created by the database at the time of data insertion into the partition. Range or interval partitioning is often used with DATE type columns. Interval partitioning is useful for data maintenance operations over routine database administration. We should decide on range or interval partitioning when we have very large, frequently scanned tables or when we have to maintain rolling maintenance windows for the database operations.
Similar to table partitioning, we can do index partitioning. We can partition any un-clustered index or index on a cluster table and mix partitioned and non-partitioned indexes with partitioned and non-partitioned tables. We can also have partitioned or non-partitioned indexes on partitioned or non-partitioned tables. But partitioned indexes are generally more complicated to maintain than partitioned tables. With online transaction processing (OLTP) applications, use global indexes and local prefixed indexes for reduced number of index partition queries. For analytical (OLAP) applications, use local non-prefixed indexes as index partitions can be scanned in parallel.
For data warehouse applications, we can compress the data in a partitioned table on a partition basis. Compressing data will help to store data efficiently in large data warehouses, where most part of the data is static and historic. When a table takes less space on disk, then database query performance also will improve.
For OLTP applications, we can easily remove data from database tables and port data onto lower cost storage tier devices with the help of partitioned tables. Using database DROP or TRUNCATE commands, older data can be removed from the database table. While the drop command will remove the data, partition metadata and related local index partitions, a truncate command will only remove the data. Using MOVE or MERGE commands will help the DBA to relocate older partitions to cheaper storage media.
While this discussion is by no means exhaustive of Oracle Database 11g partitioning options, your database management team should decide on data archival, availability, and retention requirements of key database applications to choose the mix of partition strategies. The right mix of database partitioning techniques will improve database uptime as well as operational excellence.