SHOW STATISTICS Syntax Elements - Advanced SQL Engine - Teradata Database

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.