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

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
uqf1592445067244.ditamap
dita:ditavalPath
uqf1592445067244.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

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;