SHOW STATISTICS Examples | Teradata Vantage - Example: SHOW STATISTICS for COLLECT STATISTICS SQL Text Only - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Suppose you have collected the following statistics on table orders.

     COLLECT STATISTICS 
       COLUMN o_orderdatetime ON orders;
     COLLECT STATISTICS 
       USING SAMPLE 
       COLUMN CAST(o_orderdatetime AS DATE) AS orderdate ON orders;
     COLLECT STATISTICS 
       USING SAMPLE 
       COLUMN o_orderpriority ON orders;
     COLLECT STATISTICS 
       USING SAMPLE 10 PERCENT 
       COLUMN o_custkey ON orders;
     COLLECT STATISTICS 
       USING SAMPLE 10 PERCENT 
       COLUMN o_orderkey ON orders;
     COLLECT STATISTICS 
       USING MAXINTERVALS 250 AND MAXVALUELENGTH 15
       COLUMN (o_orderdatetime, o_orderkey) ON orders;
     COLLECT STATISTICS 
       COLUMN o_totalprice ON orders;

The following SHOW STATISTICS request does not specify the VALUES option, so it reports only the SQL text for the COLLECT STATISTICS requests listed above, grouping them by the USING option used to collect the statistics.

     SHOW STATISTICS ON orders;
     COLLECT STATISTICS 
USING NO SYSTEM SAMPLE
AND SYSTEM MAXINTERVALS 
AND SYSTEM MAXVALUELENGTH
 COLUMN o_orderdatetime,
  COLUMN o_totalprice
ON orders;
     COLLECT STATISTICS 
USING SYSTEM SAMPLE 
AND SYSTEM MAXINTERVALS 
AND SYSTEM MAXVALUELENGTH
 COLUMN CAST(o_orderdatetime AS DATE) AS OrderDate, 
  COLUMN o_orderpriority
ON orders;
     COLLECT STATISTICS 
USING SAMPLE 10 PERCENT 
AND SYSTEM MAXINTERVALS 
AND SYSTEM MAXVALUELENGTH
 COLUMN o_custkey, 
  COLUMN o_orderkey
ON orders;
     COLLECT STATISTICS 
USING NO SYSTEM SAMPLE 
AND MAXINTERVALS 250
AND MAXVALUELENGTH 15
 COLUMN (o_orderdatetime, o_orderkey) 
ON orders;