7 Using Partitioning in an Online Transaction Processing Environment

Deciding Whether or not to Partition Indexes

Due to the selectivity of queries and high concurrency of OLTP applications, the choice of the right index strategy is indisputably one of the most important decisions for the use of partitioning in an OLTP environment. The following basic rules help to understand the main benefits and trade-offs for the various possible index structures:

• A non-partitioned index, while larger than individual partitioned index segments, always leads to a single index probe (or scan) if an index access path is chosen; there is only one segment for a table. The data access time and number of blocks being accessed is identical for both a partitioned and a non-partitioned table.

  A non-partitioned index does not provide partition autonomy and requires an index maintenance operation for every partition maintenance operation that affects rowids (for example, drop, truncate, move, merge, coalesce, or split operations).

• With partitioned indexes, there are always multiple segments. Whenever the Oracle Database cannot prune down to a single index segment, the database has to touch more than one segment. This potentially leads to higher I/O requirements (n index segment probes compared with one probe with a nonpartitioned index) and can have an impact (measurable or not) on the runtime performance. This is true for all partitioned indexes.

  Partitioned indexes can either be local partitioned indexes or global partitioned indexes. Local partitioned indexes always inherit the partitioning key from the table and are fully aligned with the table partitions. Consequently, any kind of partition maintenance operation requires little to no index maintenance work. For example, dropping or truncating a partition does not incur any measurable overhead for index maintenance; the local index partitions will be either dropped or truncated.

  Partitioned indexes that are not aligned with the table are called global partitioned indexes. Unlike local indexes, there is no relation between a table and an index partition. Global partitioned indexes give the flexibility to choose a partitioning key that is most optimal for a performant partition index access. Partition maintenance operations normally affect more (if not all) partitions of a global partitioned index, depending on the operation and partitioning key of the index.

• Under some circumstances, having multiple segments for an index can be beneficial for performance. It is very common in OLTP environments to leverage sequences to create artificial keys; consequently you create key values that are monotonically increasing, which results in many insert processes competing for the same index blocks. Introducing a global partitioned index (for example, using global hash partitioning on the key column) can alleviate this situation. If you have, for example, four hash partitions for such an index, then you now have four index segments you are inserting data into, reducing the concurrency on these segments by a factor of four for the insert processes.

With less contention, the application can support a larger user population. The following example shows the creation of a unique index on the order_id column of the orders table. The order_id in the OLTP application is filled using a sequence number. The unique index uses hash partitioning in order to reduce contention for the monotonically increasing order_id values. The unique key is then used to create the primary key constraint.

CREATE UNIQUE INDEX orders_pk
ON orders(order_id)
GLOBAL PARTITION BY HASH (order_id)
( PARTITION p1 TABLESPACE tbs1
, PARTITION p2 TABLESPACE tbs2
, PARTITION p3 TABLESPACE tbs3
, PARTITION p4 TABLESPACE tbs4
) NOLOGGING;

ALTER TABLE orders ADD CONSTRAINT orders_pk
PRIMARY KEY (order_id)
USING INDEX;

Enforcing uniqueness is important database functionality for OLTP environments. Uniqueness can be enforced with non-partitioned as well as with partitioned indexes. However, since partitioned indexes provide partition autonomy, the following requirements must be met to implement unique indexes:

• A non-partitioned index can enforce uniqueness for any given column or combination of columns. The behavior of a non-partitioned index is no different for a partitioned table compared to a non-partitioned table.

• Each partition of a partitioned index is considered an autonomous segment. To enforce the autonomy of these segments, you always have to include the partitioning key columns as a subset of the unique key definition.

  • Unique global partitioned indexes must always be prefixed with the partitioning columns.

  • Unique local indexes must have the partitioning key of the table as a subset of the unique key definition.

Impact of a Partition Maintenance Operation on a Partitioned Table with Local Indexes

Whenever a partition maintenance operation takes place, Oracle locks the affected table partitions for any DML operation. Data in the affected partitions, with the exception of a DROP or TRUNCATE operation, is still fully accessible for any SELECT operation. Since local indexes are logically coupled with the table (data) partitions, only the local index partitions of the affected table partitions have to be maintained as part of a partition maintenance operation, enabling the most optimal processing for the index maintenance.

For example, when you move an older partition from a high end storage tier to a low cost storage tier, the data and the index are always available for SELECT operations; the necessary index maintenance is either to update the existing index partition to reflect the new physical location of the data or, more commonly, a move and rebuild of the index partition to a low cost storage tier as well. If you drop an older partition after you have archived it, then its local index partitions get dropped as well, enabling a split-second partition maintenance operation that only affects the data dictionary.

Impact of a Partition Maintenance Operation on Global Indexes

Whenever a global index is defined on a partitioned or non-partitioned table, there is no correlation between a distinct table partition and the index. Consequently, any partition maintenance operation affects all global indexes or index partitions. As with tables containing local indexes, the affected partitions are locked to prevent DML operations against the affected table partitions. However, unlike the index maintenance for local indexes, any global index will still be fully available for DML operations and will not affect the online availability of the OLTP system. Conceptually and technically, the index maintenance for global indexes for a partition maintenance operation is comparable to the index maintenance that would become necessary for a semantically identical DML operation.

For example, dropping an old partition is semantically equivalent to deleting all the records of the old partition using the SQL DELETE statement. In both cases, all index entries of the deleted data set have to be removed from any global index as a normal index maintenance operation which will not affect the availability of an index for SELECT and DML operations. In this scenario, a drop operation represents the most optimal approach: data is removed without the overhead of a conventional DELETE operation and the global indexes are maintained in a non-intrusive manner.

Common Partition Maintenance Operations in OLTP Environments

The two most common partition maintenance operations are the removal of data and the relocation of data onto lower cost storage tier devices.

ALTER TABLE orders DROP PARTITION p_before_jan_2006
UPDATE GLOBAL INDEXES;

ALTER TABLE orders
MERGE PARTITIONS p_2006_jan,p_2006_feb
INTO PARTITION p_before_mar_2006 COMPRESS
TABLESPACE ts_low_cost
UPDATE INDEXES;