When Teradata compression becomes pressing
Have you noticed your data warehouse nearing capacity or a significant increase in Current Perm for specific tables during data loading?
Part of the Teradata Compression and Space Management guide.
Implement compression as a supplement to your space management strategy.
This article concerns multivalue compression (MVC), one of several Teradata compression mechanisms. Frequently occurring values are represented compactly using table-header metadata; this is not a description of all compression.
Before exploring the opportunities, it is important to acknowledge the constraints:
- Not all table types can be compressed; volatile tables are exempt.
- Compression is not supported on index or partitioning columns.
- VARCHAR and TIMESTAMP MVC succeeded on Teradata Database 20.00.28.81 in the audit. Check the release-specific datatype and object restrictions rather than excluding these types universally.
- You can only add compression values while space remains available in the table header section.
Now, what actions are necessary and advisable?
Ideally, reliable data demographics or forecasts should already be available in tabular form. If not, conduct a row count of the attributes you wish to compress and sort them by descending order of row number.
Compression is effective for attributes with few values, particularly for constant flags or type code columns in large tables with millions of records. We advise against compressing IDs or columns with a unique value for each row. Columns with an even distribution of numerous distinct values do not typically yield significant compression results.
A budget approach for getting it done
Deep technical knowledge is not required for selecting columns, gathering documentation, and making recommendations. Use the following method as a quick guide to getting results efficiently.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookApply the following filters to all columns in every table of your database:
Technical filter1:
Eliminate columns that cannot be compressed due to their technical function, such as primary index or partition columns. This filtering is irreversible at this stage and can only be changed by modifying the physical data model.
Technical filter 2:
Filter out columns with incompatible data types for compression. This criterion is more lenient because data types can be modified if the initial design relied more on speculation than caution.
A global minimum benefit filter can be used to eliminate tables that fall below the current size and have no further growth expected. If small tables are not present in the database or if you prefer not to exclude them immediately, you can rank them based on non-technical criteria such as usage in reporting.
Now, we will assess the remaining table columns to determine the concentration of values in each. A column with a high concentration of only a few values is more suitable for compression than one with numerous values spread uniformly. To measure this concentration, we will use the Herfindahl-Index (HI), a standard market concentration measure. The higher the value of the HI for a column, the more potential there is for compression.
To store the numeric basis of our decision, we keep a record of the HI value in a register table like this:
— COLUMN DEMOGRAPHICS REGISTER
CREATE MULTISET TABLE P_1111_A.DMnn_COL_HI_INDEX ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
STATUS_DT DATE FORMAT 'YYYY-MM-DD',
TableName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
ColumnName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
HI_INDEX DECIMAL(38,18))
PRIMARY INDEX ( TableName, ColumnName );
The original catalog-wide HHI generator is withdrawn: its generated INSERT supplied five expressions to a four-column target. Use the four-expression pattern below as a design outline; identifier quoting, NULL/empty tables and numeric precision must be handled before building a general generator.
— HHI INDEX CALCULATION PREPARATION
-- Design outline, not a catalog-wide executable generator:
-- INSERT target columns: STATUS_DT, TableName, ColumnName, HI_INDEX
-- SELECT CURRENT_DATE, 'table_name', 'column_name',
-- SUM((value_count / total_count) ** 2)
-- Compute counts using appropriate decimal precision and guard total_count=0.
;
The corrected projection was exercised on a three-row fixture and produced HHI approximately 0.555556. This does not validate a complete metadata-driven generator.
Per the table, we select those columns with an HI high enough to prepare an insert statement of compression value candidates. For documentation purposes, we create a second register table first:
— COLUMN COMPRESSION CANDIDATES
CREATE MULTISET TABLE P_1111_A.DMnn_COL_COMP_CAND ,
NO FALLBACK ,
NO BEFORE JOURNAL ,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(
STATUS_DT DATE FORMAT 'YYYY-MM-DD',
TableName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
ColumnName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
COL_VALUE CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
VALUE_SHARE DECIMAL(18,2) )
PRIMARY INDEX ( TableName, ColumnName, COL_VALUE ); -- FIND ALL COMPRESSION VALUE CANDIDATES
-- Design outline, not a catalog-wide executable generator:
-- INSERT target columns: STATUS_DT, TableName, ColumnName, COL_VALUE, VALUE_SHARE
-- SELECT CURRENT_DATE, 'table_name', 'column_name', value,
-- 100 * value_count / NULLIFZERO(total_count)
-- Define NULL representation and type-safe value serialization explicitly.
The original candidate generator omitted ColumnName and is withdrawn. Corrected projections produced a 66.67% candidate share in the three-row fixture. Review and validate a complete generator before using these registers for deployment.
Register tables facilitate the periodic insertion of updated demographic information using the STATUS_DT field to record demographic changes.
Before finalizing your deployment list, consider the long-term perspective of your compression project.
What is the frequency of your compression list updates?
How frequently do you anticipate it will be required?
What is the level of volatility of the column value set over time?
As long as they exist, Yes-No flags will hold either Yes or No, while daily account balances or order numbers are temporary.
Teradata cannot answer these questions for you. Instead, the answers should come from discussions with business representatives and management — or from your own investigation if those conversations are not possible.
A success story about Teradata compression to inspire you
A year ago, we reviewed numerous tables with little or no compression applied. The database had reached its capacity limit, and acquiring additional space of the required scale was not feasible. An initial examination of the tables’ contents showed that compression was a viable option.
The following figures are historical project observations, not a reproduced benchmark or guaranteed compression ratio:
While
- No other changes than compression were implemented and
- working in a production environment with daily growth,
- Over 1 TB of space was saved in total,
- an average reduction of 25% and 76.2% on a table level.
The result gave us many months more for daily production!
It paved the way for other necessary activities, leading to even more data being inserted.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book