Search This Blog

Wednesday, March 17, 2010

concept of indexes in teradata

UPI, NUPI:
Teradata uses primary index to distribute data across AMPs.
PI access is always one amp operation as teradata knows exactly where the record is.
So both UPI, NUPI results in one amp operation.
Where clause on PI with UPI may return 0 to 1 record
Where clause on PI with NUPI may return 0 to many records.

USI, NUSI:
Secondary index provides an alternate path to access data.
Index creates sub table on all AMPs.
It stores index value, index rowhash, rowid of record. So when ever you insert data into a table having index, DB also makes corresponding entries into index sub tables.
USI is 2 AMP operation. First teradata hashes index value, goes to a particular AMP, looks for index rowhash, Then gets the index value and also rowid where record is present. Its definitely much faster than doing Full table scans.
NUSI is all AMP operation but not full table scan. In case of NUSI, each index sub table stores only its values. So if you limit on a NUSI column, Each AMP will search for the limit you apply and returns records if present.

No comments:

Post a Comment