The Importance of Nonprefixed Indexes

Nonprefixed indexes 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, but 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

It is more expensive to probe into a nonprefixed index than to probe into a prefixed
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.

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.Oracle takes advantage of the fact that a local index is
equipartitioned with the underlying table to prune partitions based on the partition
key.

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.

Following table compares Prefixed Local, Nonprefixed Local, and Global Indexes

Posted in: Index