16.10 - Identifying Uncompressed, Single-Value Compressed, and MultiValue Compressed Tables - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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.