DWHPro Insights

Shrinking Teradata Tables: Reduce Table Size by 90% with This Simple Trick

By Roland Wenzlofsky ·

Shrinking Teradata Tables: Reduce Table Size by 90% with This Simple Trick

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

The 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
teradata primary key
How can the table be only a tenth of the size?

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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam