What is the role of a Unique Secondary Index (USI) in data access?

A Unique Secondary Index (USI) plays a crucial role in data access by providing a mechanism for efficient querying without altering the data distribution across the Teradata system. Unlike the primary index, which determines how data is distributed across the nodes in the system, a Unique Secondary Index does not participate in data distribution. Instead, it serves as an additional access path that allows users to retrieve rows based on non-primary key columns, ensuring that the rows returned are unique according to the index definition.

Since a USI ensures that no duplicate values can exist in the indexed columns, it allows for efficient lookups, and the unique nature contributes to maintaining data integrity. The designed architecture separates the indexing mechanism from the distribution of data, which is essential for performance in large databases where efficient access to data is critical.

By providing an alternate method to access data, a Unique Secondary Index enhances query performance without affecting how the data is physically stored in the system. The role of a USI is thus reflective of its design and purpose in database architecture.