Despite implementing Multivalue Compression to minimize the size of our tables, we still require additional space.
Shrinking A Teradata Table To A Minimum Size
I once used a trick that reduced an already optimized table with multivalue compression to just 10% of its original size.
This is a pattern worth watching for in your own tables.
I will illustrate using the table below:
CREATE SET TABLE DWHPRO.Sales
(
SalesDate DATE FORMAT 'YY/MM/DD' NOT NULL,
Customer_Category_Cd CHAR(5) NOT NULL COMPRESS ('10000','20000'),
SalesId CHAR(50) NOT NULL,
Sales_Main_Type_Cd CHAR(1) NOT NULL COMPRESS ('A','B','C','D','E'),
Sales_Sub_Type_Cd CHAR(5) NOT NULL COMPRESS ('00001','00002','00003'),
Sales_Value_Cd CHAR(5) NOT NULL COMPRESS ('VAL01','VAL02','VAL03'),
Sales_Class_Cd CHAR(5) NOT NULL COMPRESS ('Cla01','Cla02'),
Group_SalesId CHAR(50) NOT NULL COMPRESS (''),
Currency CHAR(3) NOT NULL COMPRESS ('EUR','USD','AUD','NZD'),
Final_Flag INTEGER NOT NULL COMPRESS 0 ,
Price DECIMAL(38,2) COMPRESS (0.00, 100.00,200.00)
) PRIMARY INDEX (SalesId)
PARTITION BY RANGE_N(
SalesDate BETWEEN DATE '2019-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY,NO RANGE, UNKNOWN)
UNIQUE INDEX (SalesDate,Customer_Category_Cd,SalesId,Sales_Main_Type_Cd ,Sales_Sub_Type_Cd,Sales_Value_Cd,Sales_Class_Cd ,Group_SalesId ,Currency ,Final_Flag );
SalesId is the primary index that distributes efficiently and is frequently utilized for joining.
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 bookThe 90% reduction is a historical observation, not a guaranteed saving. Removing a USI removes enforced uniqueness and may increase SET duplicate checking; ETL checks do not provide equivalent protection against concurrent writers. Preserve integrity and measure the complete workload.
The intent is to prevent the insertion of duplicate primary keys into the table.
Initially, this solution appears reasonable, but it ultimately proves very costly. I will explain the reason shortly. First, let’s consider the table’s size:
SELECT SUM(CURRENTPERM) / (1024**3) AS Size_GiB FROM DBC.TABLESIZE
WHERE DatabaseName = 'DWHPRO' AND TableName = 'Sales';
-> 125,50 GiB
Next, we must determine the table’s size after removing the USI.
DROP UNIQUE INDEX (SalesDate,Customer_Category_Cd,SalesId ,Sales_Main_Type_Cd,Sales_Sub_Type_Cd,Sales_Value_Cd,
Sales_Class_Cd ,Group_SalesId ,Currency ,Final_Flag)
ON DWHPRO.Sales;
SELECT SUM(CURRENTPERM) / (1024**3) AS Size_GiB FROM DBC.TABLESIZE
WHERE DatabaseName = 'DWHPRO' AND TableName = 'Sales';
-> 12,50 GiB

The Reason For A Huge Table Size
The USI is uncompressed and essentially duplicates the base table due to the inclusion of nearly all columns.
Teradata Primary Key Check Solution
In my opinion, creating secondary indexes solely to enforce the primary key is a flawed practice.
Filter size metadata by DatabaseName as well as TableName, and label division by 1024**3 as GiB. The repaired COMPRESS/column-list parentheses allowed the DDL to execute, but no 90% storage benchmark was reproduced.
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