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 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. --------------------------------------------------------------------------- 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.