16.10 - Obtaining Information about Tables with Compressed Data Blocks - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Use different methods to obtain different types of information about block-level compressed tables.

Estimated Space Savings Percentage for Manually Compressed Tables

To see the estimated space savings percentage for manually compressed BLC tables, issue a SELECT request on the BLCCompRatio column of the DBC.StatsV, DBC.TableStatsV, or DBC.TempTableStatsV views. For example:

SELECT blccompratio from dbc.statsv WHERE tablename = 'cvis001';
 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.
  BLCCompRatio
--------------
            86

File System Information in an SQL Table

To see Ferret SHOWBLOCKS-like file system information in an SQL table, use the CreateFsysInfoTable and PopulateFsysInfoTable macros.

The following example creates a volatile, non-fallback table to hold a small display of disk block information:

exec dbc.createfsysinfotable('targetdatabasename','targettablename','volatile','n','showblocks','s');

The next example populates that volatile table with data block histogram and block-level compression details for the specified input table:

exec dbc.populatefsysinfotable('inputdatabasename','inputtablename','showblocks','s','targetdatabasename','targettablename');

For details, see SQL Functions, Operators, Expressions, and Predicates.

Detailed Compression Statistics for Tables

To see detailed compression statistics, including the percentage of a table that is compressed, use a SHOW STATISTICS VALUES request. An XML version is also available. For example:

show summary statistics values on cvis300;
 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.
---------------------------------------------------------------------------
COLLECT SUMMARY STATISTICS
                ON Albee.cvis300
            VALUES
(
 /** TableLevelSummary **/
 /* Version               */ 6,
 /* NumOfRecords          */ 1,
 /* Reserved              */ 0.000000,
 /* Reserved              */ 0.000000,
 /* CurrSysInsertCnt      */ -1,
 /* CurrSysDeleteCnt      */ -1,
 /* CurrSysInsDelResetTS  */ TIMESTAMP '9999-12-31 23:59:59-00:00',
 /* NumOfCurrSysUpdateCols*/ 0,
 /* SummaryRecord[1] */
 /* Temperature           */ 0,
 /* TimeStamp             */ TIMESTAMP '2014-11-05 23:00:38-00:00',
 /* NumOfAMPs             */ 4,
 /* OneAMPSampleEst       */ 2092,
 /* AllAMPSampleEst       */ 2048,
 /* RowCount              */ 2048,
 /* DelRowCount           */ 0,
 /* PhyRowCount           */ 2048,
 /* AvgRowsPerBlock       */ 0.992188,
 /* AvgBlockSize (bytes)  */ 9216.000000,
 /* BLCPctCompressed      */ 100.00,
 /* BLCBlkUcpuCost (ms)   */ 0.400000,
 /* BLCCompRatio          */ 86.000000,
 /* AvgRowSize            */ 120.000000,
 /* Reserved              */ 0.000000,
 /* Reserved              */ 0.000000,
 /* Reserved              */ 0.000000,
 /* StatsSkipCount        */ 0,
 /* SysInsertCnt          */ 0,
 /* SysDeleteCnt          */ 0,
 /* SysUpdateCnt          */ 0,
 /* SysInsDelLastResetTS  */ TIMESTAMP '9999-12-31 23:59:59-00:00'
);

Estimated Compression Ratio

To see how much of a table is compressed (the estimated compression ratio), use the Ferret SHOWBLOCKS command. For example, the following command shows BLC information for primary data subtables:

SHOWBLOCKS /S

For details, see Utilities.

Statistics, Including Estimated Compression Ratio

To calculate statistics including the average block size and estimated compression ratio for tables compressed manually with BLC, issue a COLLECT STATISTICS or COLLECT STATISTICS SUMMARY request. For example, this request collects summary statistics on the employee table:

COLLECT SUMMARY STATISTICS ON employee;

For details, see SQL Data Definition Language - Syntax and Examples.

Considerations for Choosing a Method To Obtain BLC Information

For smaller tables that may not have data on all AMPs, Teradata recommends using the Ferret SHOWBLOCKS command instead of a COLLECT STATISTICS or COLLECT SUMMARY STATISTICS request. The reason is that the COLLECT STATISTICS estimate is based on sample data from a single AMP, while the SHOWBLOCKS estimate is based on a larger sample from all AMPs. The output of SHOWBLOCKS is more reliably accurate for smaller tables.

The SHOW STATISTICS VALUES and COLLECT STATISTICS SUMMARY requests provide BLC data about manually compressed tables only. They do not provide BLC data on tables automatically compressed with temperature-based compression.