15.00 - Join Indexes and the Optimizer - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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...

Qualification

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...

    unique

    skips the sort-delete steps used to remove duplicates.

    nonunique

    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