Join Indexes and the Optimizer - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Join Indexes and the Optimizer

For each base table in a query, the Optimizer performs certain processing phases to decide how a database operation that uses a join index is to be processed.


In this phase…

The optimizer...


evaluates up to 10 join indexes to choose the one with the lowest cost.

Qualification for the best plan includes one or more of the following benefits:

  • Smallest size to process
  • Most appropriate distribution
  • Ability to take advantage of covered fields within the join index
  • Analysis of results

    determines if this plan will result in unique results, analyzing only those tables in the query that are used in the join index.

    Subsequent action depends on analysis of the results.


    IF the results are...

    THEN the Optimizer...


    skips the sort-delete steps used to remove duplicates.


    determines whether eliminating all duplicates can still produce a valid plan, recognizing any case where the following things are true.

  • No column_name parenthetical clause exists
  • All logical rows will be accessed