To determine whether your existing tables present further opportunities for compression, you must first identify which of your current tables are compressed.
This SELECT request identifies uncompressed, single-value compressed, and multivalue compressed tables in databases aaaa, bbbb, and cccc that are15GB or greater in size, as specified by the phrase HAVING Current_Perm > 15000000000. You should customize this specification to the requirements of your site.
SELECT dbt.DATABASENAME, dbt.TABLENAME,
MAX(CASE WHEN (compressvaluelist IS NOT NULL)
THEN (CASE WHEN INDEX(compressvaluelist,',') > 0
THEN '3. MVC '
ELSE '2. SVC '
END)
ELSE '1. NONE'
END) AS Compress_Type,
MIN(pds.Current_Perm) AS Current_Perm
FROM DBC.columns AS dbt, (SELECT t.DATABASENAME, t.TABLENAME,
SUM(ts.CurrentPerm) AS Current_Perm
FROM DBC.Tables AS t, DBC.TableSize AS ts
WHERE t.DATABASENAME = ts.DATABASENAME
AND t.TABLENAME = ts.TABLENAME
AND ts.TABLENAME <> 'ALL'
HAVING Current_Perm > 15000000000
GROUP BY 1,2) AS pds
WHERE dbt.DATABASENAME IN ('aaaa','bbbb','cccc')
AND dbt.DATABASENAME = pds.DATABASENAME
AND dbt.TABLENAME = pds.TABLENAME
-- HAVING Compress_Type = '1. NONE'
GROUP BY 1,2
ORDER BY 1,3,4 DESC, 2;
The GROUP BY and ORDER BY conditions in this request order the data within each database by table size and compression type.
Note that the result set indicates that the only forms of compression found in databases aaaa, bbbb, and cccc in tables 15GB or greater in size are identified as NONE, SVC, and MVC, where NONE indicates no compression, SVC indicates single-value compression, and MVC indicates multivalue compression.
NONE identifies where no values are compressed; SVC identifies where values should be changed to MVC if possible to take advantage of the space savings from compressing more values in the table columns; MVC identifies where at least some space savings are achieved through multivalue compression.
Having a condition of MVC does not mean that an optimal multivalue compression state has been achieved for a table, however. You should make a serious effort to analyze the data from tables having a Compress_Type of MVC to determine whether still more space savings can be realized by multivalue compressing additional values in additional columns.
DatabaseName TableName Compress_Type Current_Perm
------------ --------- ------------- ------------
aaaa table_a NONE 47,898,088,960
aaaa table_e SVC 16,797,542,912
aaaa table_i MVC 19,040,798,720
aaaa table_j MVC 18,537,593,856
aaaa table_k MVC 18,333,077,504
aaaa table_1 MVC 15,982,028,800
bbbb table_1 NONE 20,157,521,408
bbbb table_2 SVC 113,774,842,368
bbbb table_3 SVC 29,932,489,728
bbbb table_4 SVC 19,060,238,848
bbbb table_5 MVC 240,413,969,920
bbbb table_6 MVC 191,052,663,296
bbbb table_7 MVC 106,940,743,680
bbbb table_8 MVC 102,247,339,520
bbbb table_9 MVC 77,317,453,824
cccc table_o NONE 66,612,497,920
cccc table_p NONE 24,427,379,712
cccc table_q NONE 19,321,673,216
cccc table_t SVC 16,848,895,488
cccc table_x MVC 52,900,937,728
cccc table_y MVC 21,144,027,648
This topic is based on the article Workload Toolkit—Part 4—Compression Analysis on Teradata Community - Developer Zone by David Roth of the Teradata Professional Services organization. To identify block-level compressed tables, see Finding Tables and Databases with Compressed Data Blocks.