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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
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:

Information Reported Once Across All Returned Histogram Intervals

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

Attribute Description Data Type
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 Vantage 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.

Attribute Description Data Type
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.

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

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

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

The 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, the database converts it to UNICODE.
  • If the maximum value is a not a case-specific CHARACTER or VARCHAR type, the database converts it to UPPERCASE.
  • The database may 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, the database converts it to UNICODE.
  • If the first biased value is not a case-specific CHARACTER or VARCHAR type, the database converts it to UPPERCASE.
  • The database may 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 n th 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 n th biased value is a non-LATIN CHARACTER or VARCHAR type, the database converts it to UNICODE.
  • If the n th biased value is not a case-specific CHARACTER or VARCHAR type, the database converts it to UPPERCASE.
  • The database may truncate a biased value if its size is greater than MAXVALUELENGTH.
Biased Freq n The frequency of occurrence of the n th 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.

Attribute Description Data Type
Max Value The maximum data value in the first equal height interval in the histogram.

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

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

Attribute Description Data Type
Max Value The maximum data value in the second equal height interval in the histogram.

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

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

Geospatial Histogram Header

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

Even though statistics can be collected on columns of 3D geospatial data, statistics are collected only for the X and Y coordinate values. The Z coordinate values are ignored for statistics collection.

Attribute Description Data Type
Version The Version Number for this GeoStatsHeader. Incremented each time new data members are added or altered within this header structure. SMALLINT
NumAMPs Number of AMPs that existed at COLLECT STATS time. SMALLINT
SizeOfLowResGeoGrid Cell count for the Low Resolution Grid. INTEGER
SizeOfHighResGeoGrid Cell count for the original High Resolution Grid. INTEGER
NumberHighResCells Actual number of high resolution grid cells being stored. INTEGER
MBRxLow MBR (Minimum Bounding Rectangle) surrounding all RTREE entries, Min X value. REAL
MBRyLow MBR surrounding all RTREE entries, Min Y value. REAL
MBRxHigh MBR surrounding all RTREE entries Max X value. REAL
MBRyHigh MBR surrounding all RTREE entries Max Y value. REAL
SkewMBRxLow MBR surrounding the statistics Min X value. REAL
SkewMBRyLow MBR surrounding the statistics Min Y value. REAL
SkewMBRxHigh MBR surrounding the statistics Max X value. REAL
SkewMBRyHigh MBR surrounding the statistics Max Y value. REAL
NumberNulls Number of nulls. BIGINT
NumberRows Total number of rows including nulls. BIGINT
NumberOutliers Number of entries not counted in the SkewMBR. BIGINT
AvgMBRWidth Average Width of the MBR of all rows. REAL
AvgMBRHeight Average Height of the MBR of all rows. REAL
TotalIC Total Intersects Count in the Low Resolution Grid. BIGINT
TotalDC Total Direct Count in the Low Resolution Grid. BIGINT
MudCount Count of the Mudpiles of the rows. REAL
NumberLevels Maximum Number of levels in the RTrees. BIGINT
NumberLeafBlks Maximum Number of Leaf Virtual Blocks in the RTrees. BIGINT
NumberLeafRows Maximum Number of Leaf Rows in the RTrees. BIGINT
NumberLeafEntries Maximum Number of Leaf Entries in the RTrees. BIGINT
LeafRowSpaceUtilization Maximum Space Utilization of Leaf Rows in the RTrees. BIGINT
NumberDirBlks Maximum Number of Directory Virtual Blocks in the RTrees. BIGINT
NumberDirRows Maximum Number of Directory Rows in the RTrees. BIGINT
NumberPoints Number of Points in all rows. REAL
NumberLineString Number of LineStrings in all rows. REAL
AvgSizeLineString Average number of Points in the LineStrings in all rows. BIGINT
NumberPolygon Number of Polygons in all rows. REAL
AvgSizePolygon Average number of Points in the Polygons in all rows. BIGINT
NumberGeomColl Number of Geometry Collections in all rows. REAL
AvgSizeGeomColl Average number of Points in the Geometry Collections in all rows. BIGINT
NumberMultiPoint Number of MultiPoints in all rows. REAL
AvgSizeMultiPoint Average number of Points in the MultiPoints in all rows. BIGINT
NumberMultiLineString Number of MultiLineStrings in all rows. REAL
AvgSizeMultiLineString Average number of Points in the MultiLineStrings in all rows. BIGINT
NumberMultiPolygon Number of MultiPolygons in all rows. REAL
AvgSizeMultiPolygon Average number of Points in the MultiPolygons in all rows. BIGINT
LowResCells Below is a list of low resolution cell attributes.
Distinct Count Distinct Count in the cell. REAL
Intersects Count Intersects Count in the cell. REAL
HighResCells Below is a list of high resolution cell attributes.
CellID Cell ID. INTEGER
Distinct Count Distinct Count in the cell. REAL
Intersects Count Intersects Count in the cell. REAL

For information about Record and Indicator modes, 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.