17.00 - VALUES - 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)

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
Report 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 “SHOW STATISTICS” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for more information about this.
The sequence numbers are required by the Archive/Recovery utility to identify the boundaries of Optimizer statistics that have been archived.

Example: SHOW STATISTICS with a VALUES Clause for a Table With Statistics Collected on a Geospatial Column

Consider the following COLLECT STATISTICS request.

     COLLECT STATISTICS
       COLUMN o_warehouseloc
     ON orders;

The following SHOW STATISTICS request reports COLLECT STATISTICS SQL text with a VALUES clause for column o_warehouseloc, a geospatial column that contains points that specify the longitude and latitude for the warehouse from which the order is to be shipped.

     SHOW STATISTICS VALUES COLUMN o_warehouseloc ON orders;
     COLLECT STATISTICS 
                COLUMN "o_warehouseloc" 
     ON "orders"
       VALUES
    (
    'EIXSTATKIND', 'GEOSPATIAL', TIMESTAMP '2013-05-01 20:35:04',
    'Version', 1,
    'NumAMPs', 4,
    'SizeOfLowResGeoGrid', 1024,
    'SizeOfHighResGeoGrid', 16384,
    'NumberHighResCells', 0,
    'MBRxLow', 2.00000000000000E 000,
    'MBRyLow', 2.00000000000000E 000,
    'MBRxHigh', 2.70000000000000E 001,
    'MBRyHigh', 2.70000000000000E 001,
    'SkewMBRxLow', 2.00000000000000E 000,
    'SkewMBRyLow', 2.00000000000000E 000,
    'SkewMBRxHigh', 1.68437500000000E 001,
    'SkewMBRyHigh', 1.60625000000000E 001,
    'NumberNulls', 0,
    'NumberRows', 174,
    'NumberOutliers', 7,
    'AvgMBRWidth', 7.00000000000000E 000,
    'AvgMBRHeight', 7.00000000000000E 000,
    'TotalIC', 42522,
    'TotalDC', 0,
    'MudCount', 1.80000000000000E 001,
    'NumberLevels', 2,
    'NumberLeafBlks', 1,
    'NumberLeafRows', 3,
    'NumberLeafEntries', 44,
    'LeafRowSpaceUtilization', 568,
    'NumberDirBlks', 1,
    'NumberDirRows', 1,
    'NumberPoints', 0.00000000000000E 000,
    'NumberLineString', 0.00000000000000E 000,
    'AvgSizeLineString', 0,
    'NumberPolygon', 1.74000000000000E 002,
    'AvgSizePolygon', 125,
    'NumberGeomColl', 0.00000000000000E 000,
    'AvgSizeGeomColl', 0,
    'NumberMultiPoint', 0.00000000000000E 000,
    'AvgSizeMultiPoint', 0,
    'NumberMultiLineString', 0.00000000000000E 000,
    'AvgSizeMultiLineString', 0,
    'NumberMultiPolygon', 0.00000000000000E 000,
    'AvgSizeMultiPolygon', 0,
    'LowResCells', 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
    1.62000000000000E 002, 0.00000000000000E 000,
            o                        o
            o                        o
            o                        o
     );