17.10 - Response Sequences for Detailed Statistics (Record and Indicator Modes) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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 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.

Vantage 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.

Vantage 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, Vantage converts it to UNICODE.
  • If the minimum value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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.

Vantage 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, Vantage converts it to UNICODE.
  • If the modal value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage might truncate a modal value if its size is greater than MAXVALUELENGTH.
Max Value The maximum data value in a histogram interval.

Vantage 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, Vantage converts it to UNICODE.
  • If the maximum value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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, Vantage converts it to UNICODE.
  • If the first biased value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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 Value n 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, Vantage converts it to UNICODE.
  • If the nth biased value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage might truncate a biased value if its size is greater than MAXVALUELENGTH.
Biased Freq n 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.

Vantage 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, Vantage converts it to UNICODE.
  • If the maximum value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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.

Vantage 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, Vantage converts it to UNICODE.
  • If the modal value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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.

Vantage 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, Vantage converts it to UNICODE.
  • If the maximum value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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.

Vantage 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, Vantage converts it to UNICODE.
  • If the modal value is a non-case specific CHARACTER or VARCHAR type, Vantage converts it to UPPERCASE.
  • Vantage 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, B035-2414, Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for information about Record and Indicator modes.