17.10 - SHOW STATISTICS Syntax Elements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
IN XML
Reports the output in XML format.
This output format can be useful for advanced processing of optimizer statistics such as graphical display, various transformations, and so on.
The XML schema for the output produced by this option is maintained in:
http://schemas.teradata.com/queryplan/queryplan.xsd
SUMMARY
Reports table-level SUMMARY statistics such as cardinality, average block size, and average row size for the specified column or index.
CURRENT
Reports extrapolated table-level summary statistics.
You can only use this option with the Optimizer form of SHOW STATISTICS.
STATISTICS
STATS
STAT
Statistics to be shown were saved as statistics_name.
VALUES
Displays the create text for the specified database object and the detailed statistics that have been collected on it using a COLLECT STATISTICS statement.
You must specify the VALUES keyword to report the detailed statistics for the specified database object.
SEQUENCED
Reports a sequence number with detailed statistics output.
This option is not valid unless you also specify VALUES.
Because the spool file size for each group of statistics is limited to a maximum of 64KB, the database uses the sequence numbers within each set of statistics reported to order the spool file chunks within each set. See Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184 for more information about this.
UNIQUE
The index for which the SQL create text or create text and detailed statistics are to be reported is unique.
INDEX
Report the SQL create text or create text and detailed statistics for an index.
This option is not valid for geospatial indexes.
index_name
Name of the named index for which the SQL create text or create text and detailed statistics are to be reported.
This option is not valid for geospatial indexes.
ALL
The ALL option was used to create the index whose detailed statistics are to be reported.
This option is not valid for geospatial indexes.
column_name_1
Names of the index columns for which detailed statistics are to be reported.
ORDER BY VALUES

The reported index columns that were value-ordered when they were collected.

VALUES is the default.

ORDER BY HASH
The reported index columns that were hash-ordered when they were collected.
column_name_2
Name of the column on which detailed index statistics are to be reported.

column_specification

column_name

Name of the column on which statistics were collected.

statistics_name
Name specified for the statistics when they were collected by a COLLECT STATISTICS request.

table_specification

TEMPORARY
The SQL create text or the SQL create text and the collected statistics are to be displayed is for a global temporary table.
database_name
user_name
Name of the containing database or user for database_object_name.
table_name_1
Name of one of the following database objects whose summary statistics and, optionally, detailed statistics are to be reported.
join_index_name
Name of join index whose summary statistics and, optionally, detailed statistics are to be reported.
hash_index_name

Name of the hash index whose summary statistics and, optionally, detailed statistics are to be reported.

qcd_name

Statistics to be retrieved are found in the QCD. TableStatistics table of the specified QCD database or user.

FOR QUERY query_ID

Unique identifier for the set of statistics to be found in QCD.TableStatistics.QueryId.

The default value is 0.

SAMPLEID statistics_id

Unique identifier for the given column or index in QCD.TableStatistics.StatisticsId.

USING MODIFIED

Reports the modified statistics stored in QCD.TableStatistics.ModifiedStats.

If you do not specify USING MODIFIED, the database reports the unmodified statistics stored in QCD.TableStatistics.StatisticsInfo.