16.10 - Cardinality and Uniqueness Statistics for the Reasonable Indexed Join Examples - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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;