Real Rules to Qualify as Teradata Index

Indexes in teradata

Indexing is one of the most important features of the Teradata RDBMS. In the Teradata RDBMS, an index is used to define row uniqueness and retrieve data rows, it also can be used to enforce the primary key and unique constraints for a table.

The Teradata RDBMS support five types of indexes

  • Unique Primary Index (UPI) 
  • Unique Secondary Index (USI) 
  • Non-Unique Primary Index (NUPI) 
  • Non-Unique Secondary Index (NUPI) 
  • Join Index 
The typical index contains two fields: a value and a pointer to instances of that value in a data table. Because the Teradata RDBMS uses hashing to distribute rows across the AMPs, the value is condensed into an entity called a row hash, which is used as the pointer.

The row hash is not the value, but a mathematically transformed address. The Teradata RDBMS uses this transformed address as a retrieval index.

The following rules apply to the indexes used in the Teradata Relation database:

  • An index is a scheme used to distribute and retrieve rows of a data table. It can be based on the values in one or more columns of the table. 
  • A table can have a number of indexes, including one primary index, and up to 32 secondary indexes. 
  • An index for a relational table may be primary or secondary, and may be unique or non-unique. Each kind of index affects system performance, and can be important to data integrity. 
  • An index is usually defined on a table column whose values are frequently used in specifying WHERE constraints or join conditions. 
  • An index is used to enforce PRIMARY KEY and UNIQUE constraints. 

Read more


Popular posts from this blog

SQL for Quantile Function in Teradata

3 Uses of SAMPLE function in Teradata

All You Need NULL Value Functions