16.10 - Table Form: Data Demographics for Single-Column Database Objects - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

This topic explains how to derive column demographics and how to add that information to the Table form.

Capture demographic data only for access and join column sets and primary key column sets.

By collecting this information, you are emulating some of the fundamental data collection activities performed by the COLLECT STATISTICS statement. The data is used for similar purposes as well: to help you to model your physical database in a way that its performance across all systems and applications is optimal.

Sources for the Required Information

The following table points to the sources for the data demographics requested by the Table form:

Information Source
Distinct values Varies.
This parameter determines how many unique values (including nulls) exist for the specified column.
  • If an existing electronic database contains the information, query that database for the number of distinct values in the column in one of two ways.

    Examine current statistics for the table.

    Execute a simple SELECT COUNT DISTINCT request over the required rows.

  • If there is no existing electronic database that contains the information, consult with your users to make a best guess estimate.
Maximum rows/value Varies.

This parameter determines the maximum value for the specified column.

  • If there is an existing electronic database that contains the information, query that database for the value that occurs most frequently in the column in one of two ways.

    Examine current statistics for the table.

    Execute a simple SELECT MAX column_name request over the required rows.

  • If no existing electronic database contains the information, consult with your users to make a best guess estimate basing your estimate on known maxima, not averages.
Rows/null This parameter determines how many rows are null for the specified column.
  • If there is an existing electronic database that contains the information, query that database for the number of rows having a null in this in one of two ways.

    Examine current statistics for the table.

    Execute a simple SELECT COUNT column_name WHERE column_name IS NULL request over the required rows.

  • If no existing electronic database contains the information, consult with your users to make a best guess estimate basing your estimate on known maxima, not averages.
Typical rows/value This parameter determines a typical number of rows per value for the specified column.

The typical number of rows per value for a column is not necessarily the average value, and the frequency can be so widely dispersed that a reasonably typical value cannot be determined.

For information on examples of how to determine a value for this parameter, see Maximum and Typical Column Value Frequencies.

Change rating Varied, depending on how your company operates. The following items are all likely sources for this information.
  • Users
  • Enterprise data model
  • IT policies and procedures

Note that the Teradata Index Wizard uses column change ratings as a parameter in determining the candidacy of columns for use as indexes.

See the following topics for more information about how the Teradata Index Wizard uses this information:
  • SQL Request and Transaction Processing.
  • SQL Data Definition Language.
The change rating codes are as follows.
  • 0 means the data for this column never changes.

    Examples include primary key columns and columns that contain historical information.

  • 1 means the data for this column rarely changes.
  • 2 - 8 are user-determined and cover anything not covered by codes 0, 1, and 9.
  • 9 means the data for this column frequently changes.