15.00 - Demographic Trends as a Function of the Number of Columns - Teradata Database

Teradata Database Design

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

Demographic Trends as a Function of the Number of Columns

Certain characteristics of the demographics of a candidate index change in predictable ways as more columns are added to the index definition.

Selectivity, defined in more detail in “Selectivity of Indexes and Partitioning” on page 197, is a measure of the ability of an index to return a highly discriminating subset of rows from a table. The higher the selectivity, the fewer rows retrieved.

  • The number of distinct values increases because each additional index column further enhances the singularity of the row.
  • The number of rows per value decreases because the number of values increases proportionately.
  • The selectivity of the candidate index increases because any set of index column values points to fewer rows.
  • The following table summarizes these trends:

     

    Index Columns

    Number of Distinct Values

    Number of Rows Per Value

    Selectivity

    State

    Least

    Most

    Lowest

    State + Zip Code

    State + Zip Code + Last Name

    Most

    Least

    Highest