Example: SHOW SUMMARY STATISTICS with VALUES Clause - Teradata Vantage

The following SHOW SUMMARY STATISTICS request displays the COLLECT STATISTICS request with VALUES clause for all the statistics on the orders table. When statistics are collected or refreshed on any column, the database automatically updates the table-level information. The details of the table-level statistical information are displayed when you submit a SHOW STATISTICS request with a SUMMARY clause as indicated in this example.

     SHOW SUMMARY STATISTICS VALUES
     ON orders;

Table-level demographics information.

     COLLECT SUMMARY STATISTICS 
                     ON "Sales"."Orders"             
                 VALUES
(
/* Version        */  1,
/* NumIntervals   */  1,
/* Interval[1]*/
/* IntervalType      */  0,
/* Collect TimeStamp */  TIMESTAMP '2009-07-20 19:47:00',
/* OneAMPSampleEst   */  950,
/* AllAMPSampleEst   */  990,
/* RowCount          */ 1000,
/* AvgRowsPerBlock   */   20,
/* AvgBlockSize      */ 61440,
/* UncompressCPUCost */     0,
/* BLCURatio         */     1
);

Detailed statistics on column o_orderkey.

     COLLECT STATISTICS 
                  USING SYSTEM SAMPLE
                        AND MAXINTERVALS 50 
                 COLUMN "O_OrderKey" 
                     ON "Sales"."Orders"
                 VALUES
(
/* Version        */  5,
/* Collect TimeStamp */  TIMESTAMP '2009-07-20 19:47:00',
/* LastAlter TimeStamp*/ TIMESTAMP '2009-07-20 19:47:00',
/* DBSVersion     */ '14.00.00.00'
/* SamplePct      */  100,
/* UsageType     */   'S'
/* Histogram Header */
/* NumBValues       */ 12
/* NumEHIntervals   */ 2,
/* NumHistoryIntervals */ 0,
/* NumAmps        */  130,
/* NumNulls        */  0,
/* NumAllNulls     */  0,
/* AvgAmpRPV       */  0.000000,
/* Min. Value      */  101,
/* Mode Value      */  103,
/* Max Value       */  506
/* Mode Freq.      */  2562150610,
/* Mode FreqPNulls */  0,
/* NumPNullsDistVals*/ 0,
/* Total Values    */  164,
/* Total Rows      */  5179858957,
/* Biased Value1    */  101,
/* Biased Freq1     */  1862561589,
/* Biased Value2    */  102,
/* Biased Freq2     */  167221704,
/* Biased Value3    */  103,
/* Biased Freq3     */  2562150610,
/* Biased Value4    */  108,
/* Biased Freq4     */  415231432,
/* Biased Value5    */  115,
/* Biased Freq5     */  8712443,
/* Biased Value6    */  116,
/* Biased Freq6     */  68543086,
/* Biased Value7    */  500,
/* Biased Freq7     */  16633628,
/* Biased Value8    */  501,
/* Biased Freq8     */  5461522,
/* Biased Value9    */  502,
/* Biased Freq9     */  20504730,
/* Biased Value10   */  503,
/* Biased Freq10    */  41812047,
/* Biased Value11   */  504,
/* Biased Freq11    */  10215418,
/* Biased Value12   */  506,
/* Biased Freq12    */  778243
/* Equal-Height Interval[1]*/
/* Max Value*/      250
/* Mode Value*/     200
/* Mode Freq */     5005
/* Other Values*/    50
/* Other Rows  */   10000
/* Equal-Height Interval[2]*/
/* Max Value*/      500
/* Mode Value*/     400
/* Mode Freq */     2500
/* Other Values*/    100
/* Other Rows  */   15000
);