17.10 - Table Form: Data Demographics for Single-Column Database Objects - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
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
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.