Selecting Indexes for Star Joins - Analytics Database - Teradata Vantage

This topic provides guidelines for selecting indexes for use in star joins. These are rules of thumb only—a more complete performance model is required in order to select indexes that optimize the entire mix of join queries on the table.

Create Indexes on Join Columns for Each Star Join

The performance of a star join can be significantly improved if you create an index on the collection of join columns of the large table so that redistributing and sorting the large table are avoided. If a large table is involved in more than one star join, create an index on the collection of join columns associated with each star join.

For example, if the large table Widgets is joined with the small tables color, shape, and size using the collection of columns (color, shape, size) in one star join, and with the small tables period, state, and country using the collection of columns (period, state, country) in another star join, then you can create the following indexes on Widgets to be used in those star joins:

• Primary index on (color, shape, size).
• Nonunique secondary index on (period, state, country).

Star Join Criteria for Selecting an Index Type

You must decide the type of index that is to be created on each collection of join columns of a large table. When making that decision, consider the following guidelines:

• A primary index is the best index for star joins.
• Each table can have only one primary index.
• The Optimizer does not use USIs and NUSIs for star joins when the estimated number of rows to be retrieved is large.

  Applications of NUSIs and USIs for star joins are limited, so verify that any index created on a large table is used by the Optimizer.

  If a NUSI or USI is used, the rowIDs are retrieved with a nested join, after which a rowID join is used to retrieve the data rows. RowID joins can be ineffective.

Star Join Criteria When Any Join Column Can Be the Primary Index

If any collection of join columns meets the criteria for a good candidate primary index (that is, has enough unique values to guarantee that the large table is distributed evenly across the AMPs), consider the following guidelines:

For example, if the star join between Widgets and (period, state, country) is used more often than the star join between Widgets and (color, shape, size), create the primary index on (period, state, country).

However, if the former join selects far fewer number of rows than the latter join, associating the primary index with the latter join (on columns color, shape, and size) may be better.

Performance Modeling: Optimizing All Join Queries

To optimize the entire mix of join queries, design a more complete performance model for your database.

For example, you may have a relatively short star join query that is used more frequently than an extremely long query.

Selecting the primary index favoring the long query may be better, even though the guidelines indicate otherwise. This is because the benefit of the long query may be great compared to the cost of the short query, and the combination of joins results in a net gain in performance.

Not all join columns of the small tables must join with the large table index in an LT/ST-J1a (Reasonable Indexed) star join.

Using a Common Set of Star Join Columns in the Primary Index

If more than one combination of large table columns is used in different star joins, and if the combinations overlap, the primary index must consist of the common set of these combinations (if the set is qualified for the primary index).

This has the following advantages:

• Fewer indexes are required.
• More than one star join can share the same primary index.

For example, assume that the following conditions are true:

• The collection of columns (color, shape, size) of the large table widgets is joined with the small tables (color, shape, and size) of a star join
• The collection of columns (shape, size, and period) is joined with the small tables (shape, size, and period) of another star join

The primary index of widgets must be defined on the columns (shape, size) if that column set is qualified to be the primary index for widgets.