17.10 - Cardinality and Uniqueness Statistics for the Reasonable Indexed Join Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Small Table Cardinality Statistics

For all LT/ST-J1a (Reasonable Indexed Joins) examples, the following row information is given for the small dimension tables:

Attribute Cardinality
color 2
size 10
options 10

Large Table Uniqueness Statistics

The following statistics information is given for the large fact table:

Column Name Number of Unique Values
color 10
size 100
options 1000

Test Query

These examples explain the join plans and estimated time for execution of the following query, when different types of indexes (primary index, unique secondary index, nonunique secondary index) are created on the various join columns (color, size, and options) of the large table.

   SELECT …
   WHERE widgets.color = color.code
   AND   widgets.size = size.code
   AND   widgets.options = options.code;