15.10 - Field - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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

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);

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.

    See the following chapter and manuals for more information about the Teradata Index Wizard utility.

  • Teradata Index Wizard User Guide
  • Chapter 8: “The Teradata Index Wizard”