Nonunique Secondary Indexes
Nonunique secondary indexes are typically assigned to nonunique column sets that frequently appear in WHERE clause selection conditions, join conditions, ORDER BY and GROUP BY clauses, foreign keys, and miscellaneous other conditions such as UNION, DISTINCT, and any attribute that is frequently sorted.
Note: You can define a NUSI on a row‑level security constraint column.
You can also define a simple NUSI, but not a composite NUSI, on a geospatial column.
Highly selective NUSIs are useful for reducing the cost of frequently made selections and joins on nonunique columns, and provide extremely fast access for equality conditions. This is particularly true for NoPI tables (see “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280), where the only other access method might be a full‑table scan. Note that NUSIs with low selectivity can be less efficient than a full‑table scan.
NUSIs are also useful for range access and in-list conditions and for geospatial indexes.
Also note the following about NUSIs: