15.00 - Identifying Compressed Tables for Further Opportunities to Save Space - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Identifying Compressed Tables for Further Opportunities to Save Space

This topic discusses no compression, single-value compression (SVC), and multi-value compression (MVC).

To determine whether your existing tables present further opportunities for compression, you must first identify which of your current tables are compressed in some way.

This SELECT request identifies all such 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 multi‑value compression.

NONE identifies a condition where no values are compressed, so no space savings are gained from implementing multi‑value compression; SVC identifies a condition that should changed to MVC if possible to take advantage of the space savings offered by compressing more values in the table columns; MVC identifies a condition where at least some space savings are achieved through multi‑value compression.

Having a condition of MVC does not mean that an optimal multi‑value 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 multi‑value compressing additional values in additional columns.

 

This topic is based on the article Workload Toolkit—Part 4—Compression Analysis on Teradata Developer Exchange (http://developer.teradata.com/) by David Roth of the Teradata Professional Services organization.