Obtaining Information about Tables with Compressed Data Blocks | Vantage - 17.10 - Obtaining Information about Tables with Compressed Data Blocks - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

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.

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 table to hold a small display of disk block information:

exec dbc.createfsysinfotable('targetdatabasename','targettablename','volatile','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 more information, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

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.
                ON Albee.cvis300
 /** 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:


For more information, see Teradata Vantage™ - Database Utilities, B035-1102.

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:


For more information, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

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.