Chapter 7 Teradata Database Indexes and Partitioning - Teradata Database

Teradata Database provides indexing and partitioning options for optimizing the performance of your relational databases:

You can apply primary indexes to tables and join indexes.

You can apply secondary, hash, and join indexes to all tables.

You can apply secondary indexes to join indexes.

Teradata Database primary and secondary indexes are not really indexes, but hash keys. Definitions of indexes and hash keys are provided in “Indexing and Hashing” on page 168 and “Tradeoffs Between Hashing and Indexing” on page 170.

Hash and join indexes are not true indexes; instead, they are user‑inaccessible tables that the Optimizer can use to resolve queries covered by the hash or join index without necessarily accessing the underlying base tables. The Optimizer can also use secondary, hash, and join indexes to partially cover queries, joining the partial covering result to the indexed base table to capture data that is not covered by an index.

This chapter describes Teradata Database indexing and introduces the types of indexes you can specify.

The chapter also briefly describes how the rows of tables and indexes are distributed to the AMPs using different methods of row allocation, and how rows and columns can be partitioned on each AMP.