16.10 - Field - 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

Function of Field

Captures all the columns used or referenced in a captured query plan.

Field Table Definition

The following CREATE TABLE request defines the Field table:

   CREATE TABLE Field (
     FieldID              INTEGER NOT NULL,
     RelationKey          INTEGER NOT NULL,
     Name                 VARCHAR(128) CHARACTER SET UNICODE
                          NOT CASESPECIFIC,
     FldAlias             VARCHAR(128) CHARACTER SET UNICODE
                          NOT CASESPECIFIC,
     QueryID              INTEGER NOT NULL,
     ValueAccessFrequency INTEGER,
     JoinAccessFrequency  INTEGER,
     RangeAccessFrequency INTEGER,
     ChangeRate           INTEGER,
     DataLength           INTEGER,
     StatsKind            CHARACTER(1) CHARACTER SET LATIN
                          NOT CASESPECIFIC,
     NumNulls             FLOAT,
     NumIntervals         INTEGER,
     MinValue             VARCHAR(512) CHARACTER SET UNICODE
                          NOT CASESPECIFIC,
     ModeValue            VARCHAR(512) CHARACTER SET UNICODE
                          NOT CASESPECIFIC,
     ModeFreq             FLOAT,
     TotalValues          FLOAT,
     TotalRows            FLOAT)
   PRIMARY INDEX(RelationKey)
   UNIQUE INDEX USK_FieldID_RelationKey (FieldID, RelationKey);

Attribute Definitions for Field

The following table defines the Field table attributes:

Attribute Definition
FieldID
  • Unique column identifier within a specific table.
  • Partial USI for the table.
RelationKey
  • Unique identifier for the relation in which the column is defined within a certain database.
  • NUPI for the table.
  • Partial USI for the table.
Name The name of the captured column.
FldAlias
  • Alias name for the column.
  • Null if none exists.
QueryID The unique ID for the query.
ValueAccessFrequency The number of times the column is used with an equality condition.
JoinAccessFrequency The number of times the column is used in a join condition.
RangeAccessFrequency The number of times the column is used in a range condition.
ChangeRate The change rating value for the column.
  • If the columns is modified, ChangeRate is set to 1.
  • If the column is not modified, ChangeRate is set to 0.
DataLength The maximum length of the column.
StatsKind Defines whether the statistics are collected from the dictionary or from a QCD.

The data for this column is retrieved from interval 0 of the available statistics.

The column is set to null if no statistics are available for the column or index.

The value is set to S during index validation by the Teradata Index Wizard or the SQL DIAGNOSTIC “Validate Index” statement. Statistics for the column being analyzed are retrieved from the QCD.

  • P is dictionary statistics.
  • S is QCD statistics.
  • Null is no statistics.
NumNulls Number of nulls for the column or index.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

NumIntervals Number of intervals for the column or index.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

MinValue Minimum value for the interval.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

ModeValue Modal value for the interval.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

ModeFreq Number of occurrences of the modal value in the interval.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

TotalValues Number of unique values for the column or index in the table.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

TotalRows Cardinality of the table.

The data for this column is retrieved from interval 0 of the available statistics. The column is set to null if no statistics are available.

Field Related Topics

For more information about the Teradata Index Wizard utility, see Teradata Index Wizard User Guide.