15.00 - Example Cases for These Guidelines - Teradata Database

Teradata Database Design

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

Example Cases for These Guidelines

The following examples illustrate the guidelines stated in “Calculating Multicolumn Demographics.”

  • Consider the following table fragment. The candidate index for which multicolumn demographics are to be estimated is (ProductCode, ColorCode).
  •  

     

    ProductCode

    ColorCode

     

     

    The product (ProductCode) * (ColorCode) is true iff every product is available in every color.

  • Consider the following table fragment The candidate index for which multicolumn demographics are to be estimated is (StateCode, ZipCode).
  •  

     

    StateCode

    ZipCode

     

     

  • The product (StateCode) * (ZipCode) is not an accurate estimate of the number of distinct values in the multicolumn index because the resulting product has fewer distinct values than the product of distinct StateCode and distinct ZipCode values.
  • If you cannot pair every value in the StateCode column with every value in the ZipCode column, then the number of distinct values in the multicolumn index is approximately equal to (Number of distinct StateCode values) * (Average number of ZipCode values that can be paired with a single StateCode value).
  • For example, the number of distinct values is the product of the number of states in the US and the average number of zip codes per state, which we estimate to be 20.

    Therefore, the number of distinct values for the StateCode + ZipCode index would be roughly 50 * 20, or 1,000.

  • The same considerations hold most of the other rows per value calculations.
  • For example, if every value of column_1 can be paired validly with every value for column_2, then the result of the division is a fairly accurate estimate.

    For each value estimated, the minimum is never less than the number of column_1 rows per value divided by the number of distinct column_2 values.

    The exception is the count of Rows Null (labeled Rows/Null). See “Treating the Rows Null Counts for Composite Indexes” on page 169 for information on handling multicolumn indexes that are wholly or partially null.