Choosing the best index type in Oracle with a Performance Comparison

v What are partitioned indexes in Oracle?

v Which type is right for my environment (OLTP vs OLAP/DSS)?

v How do they compare and when should I use which type?

v What are limitations/restrictions on this way?

Index Partitioning

Partitioning indexes has recommendations and considerations in common with partitioning tables.

The rules for partitioning indexes are similar to those for tables:

• An index can be partitioned unless:

– The index is a cluster index.

– The index is defined on a clustered table.

• You can mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables:

– A partitioned table can have partitioned or nonpartitioned indexes.

– A nonpartitioned table can have partitioned or nonpartitioned indexes.

• Bitmap indexes on nonpartitioned tables cannot be partitioned.

• A bitmap index on a partitioned table must be a local index.

However, partitioned indexes are more complicated than partitioned tables because there are three types of partitioned indexes:

• Local prefixed

• Local nonprefixed

• Global prefixed

Oracle Database supports all three types. However, there are some restrictions.

For example, a key cannot be an expression when creating a local unique index on a partitioned table.

Local Partitioned Indexes

In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition.

A local index is created by specifying the LOCAL attribute.

Oracle constructs the local index so that it is equipartitioned with the underlying table.

Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions or sub-partitions, and gives them the same partition bounds as corresponding partitions of the underlying table.

Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or sub-partitions are added or coalesced.

This ensures that the index remains equipartitioned with the table.

A local index can be created UNIQUE if the partitioning columns form a subset of the index columns.

This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.

Local indexes have the following advantages:

• Only one index partition must be rebuilt when a maintenance operation other than SPLIT PARTITION or ADD PARTITION is performed on an underlying table partition.

• The duration of a partition maintenance operation remains proportional to partition size if the partitioned table has only local indexes.

• Local indexes support partition independence.

• Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.

• Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate better query access plans.

• Local indexes simplify the task of tablespace incomplete recovery.

To recover a partition or sub-partition of a table to a point in time, you must also recover the corresponding index entries to the same point in time.

The only way to accomplish this is with a local index.

Then you can recover the corresponding table and index partitions or Sub-partitions.

Local Prefixed Indexes

A local prefixed index is a type of local partitioned index where the table's partition key (or a leftmost combination of columns) is the leading column(s) in the index definition. 

This allows the database to perform partition pruning, where queries specifying the partition key in the WHERE clause can avoid searching non-relevant partitions, making queries more efficient for OLTP (online transaction processing) workloads. 

So, a local index is prefixed if it is partitioned on a left prefix of the index columns and the subpartioning key is included in the index key.

Local prefixed indexes can be unique or nonunique.

For example,

if the sales table and its local index sales_ix are partitioned on the week_num column, then index sales_ix is local prefixed if it is defined on the columns (week_num, xaction_num).

On the other hand, if index sales_ix is defined on column product_num then it is not prefixed.

This figure illustrates another example of a local prefixed index.

Key Characteristics

• Equipartitioned with the Table:

A local prefixed index uses the same partitioning key and method as the underlying table, meaning its partitions align with the table's partitions. 

• Leading Partition Key:

The defining feature is that the partition key is the first column (or the first combination of columns in a composite key) in the index's key list. 

• Supports Partition Pruning:

Because the partition key is at the start of the index, the database can efficiently locate data by only scanning the relevant partition(s) when the partition key is specified in a query's WHERE clause. 

This index type is ideal for queries that filter on the partition key, as it enables the database to quickly identify and access only the required data segments. 

Local Nonprefixed Indexes

A local index is nonprefixed if it is not partitioned on a left prefix of the index columns or if the index key does not include the sub-partitioning key.

You cannot have a unique local nonprefixed index unless the partitioning key is a subset of the index key.

The partition key is not the leading edge of the index. 

While these indexes are still partitioned along with the table (local), they do not support partition pruning for queries filtering on the partition key, as the partition key isn't the first part of the index.

They are distinguished from local prefixed indexes by the relationship between the index columns and the table's partitioning key

This figure illustrates an example of a local nonprefixed index.

Key Characteristics:

• Partitioning Key Location:

The defining characteristic of a local non-prefixed index is that the table's partitioning key columns are not the leading columns of the index definitionThe index may or may not include the partitioning key columns at all.

• Partition Alignment:

Like all local indexes, local non-prefixed indexes are partitioned in the same way as their underlying table, meaning each index partition corresponds to a single table partition.

 

• Maintenance:

Oracle automatically maintains local indexes. When a partition is added to or dropped from the table, the corresponding index partition is also added or dropped.

• Uniqueness:

A local non-prefixed index can be unique, but only if the table's partitioning key is included within the index key columns.

• Partition Pruning:

While local non-prefixed indexes can be used to access data within a specific partition, they do not inherently enable partition pruning based on the index key alone, unlike local prefixed indexes. 

If a query includes a predicate on the partitioning key, Oracle can still perform partition pruning on the table, and then use the local non-prefixed index within the pruned partition.

Local non-prefixed indexes are often utilized in scenarios where queries frequently access data based on columns other than the partitioning key,

  and the benefits of local index management and availability outweigh the potential performance implications of less direct partition pruning. 

They are commonly found in data warehousing environments or historical databases where older partitions are frequently dropped.

Global Partitioned Indexes

In a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table partitions or sub-partitions.

A global index can be range or hash partitioned, though it can be defined on any type of

partitioned table. A global index is created by specifying the GLOBAL attribute.

The database administrator is responsible for defining the initial partitioning of a global index at creation and for maintaining the partitioning over time.

Index partitions can be merged or split as necessary.

Normally, a global index is not equipartitioned with the underlying table.

There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations.

So, an index that is equipartitioned with the underlying table should be created as LOCAL.

A global partitioned index contains a single B-tree with entries for all rows in all partitions.

Each index partition may contain keys that refer to many different partitions or sub-partitions in the table.

The highest partition of a global index must have a partition bound that includes all values

that are MAXVALUE. This insures that all rows in the underlying table can be represented in the index.

 

Prefixed and Nonprefixed Global Partitioned Indexes

Ø  A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns.

Ø  A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns.

Ø  Oracle does not support global nonprefixed partitioned indexes.

Ø  Global prefixed partitioned indexes can be unique or nonunique.

Ø  Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.

 

 

 

 

 

 

Figure: Global Prefixed Partitioned Index

Management of Global Partitioned Indexes

Management of global partitioned indexes presents several challenges.

Global partitioned indexes are harder to manage than local indexes because of the following:

• When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected.

Consequently, global indexes do not support partition independence.

• When an underlying table partition or sub-partition is recovered to a point in time, all corresponding entries in a global index must be recovered to the same point in time.

Because these entries may be scattered across all partitions or sub-partitions of the index, mixed with entries for other partitions or sub-partitions that are not being recovered,                                                                                                                                          there is no way to accomplish this except by re-creating the entire global index.

Summary of Partitioned Index Types

A summary of partitioned index types is provided in this topic.

Following table summarizes the types of partitioned indexes that Oracle supports. The key points are:

• If an index is local, then it is equipartitioned with the underlying table. Otherwise, it is global.

• A prefixed index is partitioned on a left prefix of the index columns. Otherwise, it is nonprefixed.

Table: Types of Partitioned Indexes

^Footnote 1

For a unique local nonprefixed index, the partitioning key must be a subset of the index key and cannot be a partial index.

^Footnote 2

Although a global partitioned index may be equipartitioned with the underlying table, Oracle does not take advantage of the partitioning or maintain equipartitioning after partition maintenance operations such as DROP or SPLIT PARTITION.

The Importance of Nonprefixed Indexes

Nonprefixed indexes are important because they are particularly useful in historical databases.

In a table containing historical data, it is common for an index to be defined on one column to support the requirements of fast access by that column.

However, the index can also be partitioned on another column (the same column as the underlying table) to support the time interval for rolling out old data and rolling in new data.

Consider a sales table partitioned by week. It contains a year's worth of data, divided into 13 partitions.

It is range partitioned on week_no, four weeks to a partition.

You might create a nonprefixed local index sales_ix on sales. The sales_ix index is defined on acct_no because there are queries that need fast access to the data by account number.

However, it is partitioned on week_no to match the sales table. Every four weeks, the oldest partitions of sales and sales_ix are dropped and new ones are added.

Performance Implications of Prefixed and Nonprefixed Indexes

There are performance implications of prefixed and nonprefixed indexes.

With a prefixed index, the likelihood to get partition pruning is much higher than with a nonprefixed index.

If a column is part of an index, then you can assume that the column is used as a filter predicate, which automatically means some level of pruning when a filtered column is a prefixed column.

This result suggests that it is usually less expensive to probe into a prefixed index than to probe into a nonprefixed index.

If an index is prefixed (either local or global) and Oracle is presented with a predicate involving the index columns, then partition pruning can restrict application of the predicate to a subset of the index partitions.

For example, in Figure of Local Prefixed Index, if the predicate is deptno=15, the optimizer knows to apply the predicate only to the second partition of the index.

(If the predicate involves a bind variable, the optimizer does not know exactly which partition but it may still know there is only one partition involved, in which case at run time, only one index partition is accessed.)

When an index is nonprefixed, Oracle often has to apply a predicate involving the index columns to all N index partitions. This is required to look up a single key, or to do

an index range scan. For a range scan, Oracle must also combine information from N index partitions. For example, in Figure of Local Nonprefixed Index, a local index is partitioned on chkdate with an index key on acctno.

If the predicate is acctno=31, Oracle probes all 12 index partitions.

Of course, if there is also a predicate on the partitioning columns, then multiple index probes might not be necessary. Oracle takes advantage of the fact that a local index is

equipartitioned with the underlying table to prune partitions based on the partition key.

For example, if the predicate in Figure of Local Nonprefixed Index is chkdate<3/97, Oracle only has to probe two partitions.

So, for a nonprefixed index, if the partition key is a part of the WHERE clause but not of the index key, then the optimizer determines which index partitions to probe based on the underlying table partition.

When many queries and DML statements using keys of local, nonprefixed, indexes have to probe all index partitions, this effectively reduces the degree of partition independence provided by such indexes.

Table: Comparing Prefixed Local, Nonprefixed Local, and Global Indexes

Guidelines for Partitioning Indexes

There are several guidelines for partitioning indexes. When deciding how to partition indexes on a table, consider the mix of applications that must access the table.

There is a trade-off between performance and availability and manageability.  Here are some guidelines you should consider:

• For OLTP applications:

– Global indexes and local prefixed indexes provide better performance than local nonprefixed indexes because they minimize the number of index partition probes.

– Local indexes support more availability when there are partitions or sub-partitions maintenance operations on the table. Local nonprefixed indexes are very useful for historical databases.

• For DSS applications, local nonprefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.

For example, a query using the predicate "acctno between 40 and 45" on the table checks of Figure of Local Nonprefixed Index causes parallel scans of all the partitions of the nonprefixed index ix3.

On the other hand, a query using the predicate deptno BETWEEN 40 AND 45 on the table deptno of Figure of Local Prefixed Index cannot be parallelized because it accesses a single partition of the prefixed index ix1.

• For historical tables, indexes should be local if possible. This limits the effect of regularly scheduled drop partition operations.

• Unique indexes on columns other than the partitioning columns must be global because unique local nonprefixed indexes whose keys do not contain the partitioning key are not supported.

• Unusable indexes do not consume space.

Physical Attributes of Index Partitions

The command ALTER INDEX MODIFY DEFAULT ATTRIBUTES is an Oracle Database command used to change the default physical attributes (like TABLESPACE, compression, etc.) that will be inherited by future partitions of a partitioned index. 

It allows you to set new defaults that will be applied to new partitions, while not affecting existing ones, making it useful for managing partitioned index storage and performance over time. 

Default physical attributes are initially specified when a CREATE INDEX statement creates a partitioned index.

Because there is no segment corresponding to the partitioned index itself, these attributes are only used in derivation of physical attributes of member partitions.

Default physical attributes can later be modified using ALTER INDEX MODIFY DEFAULT ATTRIBUTES.

Physical attributes of partitions created by CREATE INDEX are determined as follows:

• Values of physical attributes specified (explicitly or by default) for the index are used whenever the value of a corresponding partition attribute is not specified.                                                                                                                                                  Handling of the TABLESPACE attribute of partitions of a LOCAL index constitutes an important exception to this rule in that in the absence of a user-specified                                                                                                                                  TABLESPACE value (at both partition and index levels), that of the corresponding partition of the underlying table is used.
• Physical attributes (other than TABLESPACE, as explained in the preceding) of partitions of local indexes created in the course of processing ALTER TABLE ADD PARTITION are set to the default physical attributes of each index.

Physical attributes (other than TABLESPACE) of index partitions created by ALTER TABLE SPLIT PARTITION are determined as follows:

• Values of physical attributes of the index partition being split are used.

Physical attributes of an existing index partition can be modified by ALTER INDEX MODIFY PARTITION and ALTER INDEX REBUILD PARTITION. Resulting attributes are determined as follows:

• Values of physical attributes of the partition before the statement was issued are used whenever a new value is not specified. Note that ALTER INDEX REBUILD PARTITION can be used to change the tablespace in which a partition resides.

Physical attributes of global index partitions created by ALTER INDEX SPLIT PARTITION are determined as follows:

• Values of physical attributes of the partition being split are used whenever a new value is not specified.
• Physical attributes of all partitions of an index (along with default values) may be modified by ALTER INDEX, for example, ALTER INDEX index_name NOLOGGING changes the logging mode of all partitions of index_name to NOLOGGING.

 

Modifying Default Attributes of a Table

You can modify the default attributes that are inherited for range, hash, list, interval, or reference partitions using the MODIFY DEFAULT ATTRIBUTES clause of ALTER TABLE.

For hash partitioned tables, only the TABLESPACE attribute can be modified.

Modifying Default Attributes of a Partition

To modify the default attributes inherited when creating subpartitions, use the ALTER TABLE MODIFY DEFAULT ATTRIBUTES FOR PARTITION.

The following statement modifies the TABLESPACE in which future subpartitions of partition p1 in the range-hash partitioned table reside.

SQL> ALTER TABLE employees_subpartitions   MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;

Because all subpartitions of a range-hash partitioned table must share the same attributes, except TABLESPACE, it is the only attribute that can be changed.

You cannot modify default attributes of interval partitions that have not yet been created.

To change the way in which future subpartitions in an interval-partitioned table are created, you must modify the subpartition template.

Modifying Default Attributes of Index Partitions

In a similar fashion to table partitions, you can alter the default attributes that are inherited by partitions of a range-partitioned global index, or local index partitions of partitioned tables.

For this you use the ALTER INDEX MODIFY DEFAULT ATTRIBUTES statement.

Use the ALTER INDEX MODIFY DEFAULT ATTRIBUTES FOR PARTITION statement if you are altering default attributes to be inherited by subpartitions of a composite partitioned table

 

 

FAQ:

What is the prefixed and non-prefixed index?

The index is called prefixed if the leading column(s) in the index definition is (are) the partition key column(s), otherwise it is called non-prefixed.

Can we subpartition the global index?

No, you cannot subpartition global index, but you can partition it. The only way to end up with index subpartition is to have a sub-partitioned table with local index

How to Find Whether an Index is Global or Local

By querying the column, LOCALITY, in DBA_PART_INDEXES.