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';
Result:
*** 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 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;
Result:
*** 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 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:
COLLECT SUMMARY STATISTICS ON employee;
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.