15.00 - Response Sequences for Detailed Statistics (Record and Indicator Modes) - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Response Sequences for Detailed Statistics (Record and Indicator Modes)

The result is returned as a single record parcel consisting of multiple columns whose values retain the underlying data type of the column on which statistics were collected. The columns and their data types for the table attributes reported by a SHOW STATISTICS … VALUES request when run in Record or Indicator modes are as follows:

 

             Attribute

                                Description

                              Data Type

This set of information is reported once across all the returned histogram intervals.

Version

The version number for the COLLECT STATISTICS syntax used to collect the statistics

BYTE

Collect TimeStamp

The timestamp for when statistics were first collected for the column or index set.

TIMESTAMP(0)

LastAlter TimeStamp

The timestamp for when statistics were last collected for the column or index set.

TIMESTAMP(0)

DBSVersion

The version number for the Teradata Database software on your server.

VARCHAR(32)

SamplePct

If the statistics were sampled, the sampling percentage used to collect them.

DECIMAL(5,2)

UsageType

A codes used to indicate whether the usage is Summary or Detailed.

  • S is Summary usage.
  • D is Detailed usage.
  • CHARACTER(1) LATIN

    Histogram Header

    The information that follows this heading is taken from the histogram header for the interval histogram being reported.

    NumBValues

    Number of biased values in the histogram.

    SMALLINT

    NumEHIntervals

    Number of equal height intervals in the histogram.

    SMALLINT

    NumHistoryIntervals

    Number of history records in the histogram.

    SMALLINT

    NumAmps

    Number of AMPs in the system.

    INTEGER

    NumNulls

    Row cardinality in a composite column or index set that has 1 or more nulls in columns in the set.

    REAL

    NumAllNulls

    Row cardinality in a composite column or index set that has nulls for all of the columns in the set.

    REAL

    AvgAmpRPV

    The average rows per value across all AMPs in the system for NUSIs.

    Teradata Database reports a value only for NUSIs. If the column set reported is not a NUSI, the average AMP rows per value reported is 0.

    REAL

    Min Value

    The minimum data value in a histogram interval.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the minimum value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the minimum value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a minimum value if its size is greater than MAXVALUELENGTH.
  • Mode Value

    The data value that occurs the most frequently in a histogram interval.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the modal value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the modal value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a modal value if its size is greater than MAXVALUELENGTH.
  • Max Value

    The maximum data value in a histogram interval.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the maximum value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the maximum value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a maximum value if its size is greater than MAXVALUELENGTH.
  • Mode Freq.

    The cardinality of the modal data value in a histogram interval.

    REAL

    Mode FreqPNulls

    The cardinality of the modal distinct partial nulls in a histogram interval.

    REAL

    NumPNullsDistVals

    The cardinality of the distinct partial null sets in a histogram interval.

    REAL

    Total Values

    The composite cardinality of all data values in a histogram interval.

    REAL

    Total Rows

    The composite cardinality of all rows in a histogram.

    REAL

    Biased Value1

    The data value of the first biased value in the histogram.

    Stored as the native type for the data value of the first biased value in the histogram with the following exceptions.

  • If the first biased value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the first biased value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a biased value if its size is greater than MAXVALUELENGTH.
  • Biased Freq1

    The frequency of occurrence of the first biased value.

    REAL

                                              

    Biased Valuen

    The data value of the nth biased value in the histogram.

    Stored as the native type for the data value of the nth biased value in the histogram with the following exceptions.

  • If the nth biased value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the nth biased value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a biased value if its size is greater than MAXVALUELENGTH.
  • Biased Freqn

    The frequency of occurrence of the nth biased value.

    REAL

    Equal-Height Interval [1]

    The information that follows this heading is taken from the first equal‑height interval in the histogram being reported.

    Max Value

    The maximum data value in the first equal height interval in the histogram.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the maximum value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the maximum value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a maximum value if its size is greater than MAXVALUELENGTH.
  • Mode Value

    The modal data value in the first equal height interval in the histogram.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the modal value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the modal value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a modal value if its size is greater than MAXVALUELENGTH.
  • Mode Freq

    The frequency of the modal data value in the first equal height interval in the histogram.

    REAL

    LowFrequency

    The lowest frequency in the equal height interval in the histogram.

    The Optimizer uses this value to determine the deviation of the values in an equal‑height interval compared with its modal and lowest frequencies.

    REAL

    Other Values

    The cardinality of values other than modal values in the interval.

    REAL

    Other Rows

    The cardinality of rows other than modal value rows in the interval.

    REAL

    Equal-Height Interval [2]

    The information that follows this heading is taken from the second equal‑height interval in the histogram being reported.

    Max Value

    The maximum data value in the second equal height interval in the histogram.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the maximum value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the maximum value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a maximum value if its size is greater than MAXVALUELENGTH.
  • Mode Value

    The modal data value in the second equal height interval in the histogram.

    Teradata Database returns a separate column for each reported column value.

    Stored as the native type for the column with the following exceptions.

  • If the modal value is a non‑LATIN CHARACTER or VARCHAR type, Teradata Database converts it to UNICODE.
  • If the modal value is a non‑case specific CHARACTER or VARCHAR type, Teradata Database converts it to UPPERCASE.
  • Teradata Database might truncate a modal value if its size is greater than MAXVALUELENGTH.
  • Mode Freq

    The frequency of the modal data value in the second equal height interval in the histogram.

    REAL

    Other Values

    The cardinality of values other than modal values in the interval.

    REAL

    Other Rows

    The cardinality of rows other than modal value rows in the interval.

    REAL

    See Basic Teradata Query Reference, Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems for information about Record and Indicator modes.