Nonpartitioned Indexes

You can create nonpartitioned indexes on nonpartitioned and partitioned tables. Nonpartitioned indexes are primarily used on nonpartitioned tables in data warehouse environments and in general to enforce uniqueness if the status of a unique constraint is required to be enforced in a data warehousing environment. You can use a nonpartitioned global index on a partitioned table to enforce a primary or unique key. A nonpartitioned (global) index can be useful for queries that commonly retrieve very few rows based on equality predicates or IN-list on a column or set of columns that is not included in the partitioning key. In those cases, it can be faster to scan a single index than to scan many index partitions to find all matching rows.

Unique indexes on columns other than the partitioning columns must be global because unique local nonprefixed indexes whose keys do not contain the partitioning keys are not supported. Unique keys are not always enforced in data warehouses due to the controlled data load processes and the performance cost of enforcing the unique constraint. Global indexes can grow very large on tables with billions of rows.

The following example creates a global unique index on the sales table:

CREATE UNIQUE INDEX sales_unique_ix
  ON sales(cust_id, prod_id, promo_id, channel_id, time_id)
  PARALLEL NOLOGGING;

Very few queries benefit from this index. In systems with a very limited data load window, consider not creating and maintaining it.