SHOW STATISTICS Syntax Elements - Teradata Vantage

IN XML

Reports the output in XML format.

This output format can be useful for advanced processing of optimizer statistics such as graphical display, transformations, and so on.

The XML schema for the output produced by this option is located in SchemaFiles.zip under attachments in the left pane.

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, for the specified database object, the create text and statistics collected by 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 "SEQUENCED Option" in General Usage Guidelines for SHOW STATISTICS for more information.

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

VALUES is the default.

ORDER BY HASH

The reported index columns that were hash-ordered when 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 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.

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.