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. 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.
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 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_ix are dropped and new ones are added.