Archives of the TeradataForum

Message Posted: Wed, 28 Dec 2011 @ 16:14:56 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Calculating uncompressed Size

From:		JAMES PARK

Anomy.Anom wrote:

The below query will get you all the tables with compression and their current perm size.

     > 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) COMPRESS_TYPE,
     >        MIN(pds.Current_Perm) CURRENT_PERM
     >
     >   FROM dbc.columns dbt,
     >
     >       (SELECT t.DATABASENAME,
     >               t.TABLENAME,
     >               SUM(ts.CurrentPerm) CURRENT_PERM
     >          FROM DBC.Tables t,
     >               DBC.TableSize ts
     >         WHERE t.DATABASENAME = ts.DATABASENAME
     >           AND t.TABLENAME = ts.TABLENAME
     >           AND ts.TABLENAME <> 'ALL'
     >        HAVING CURRENT_PERM > 1000000000
     >         GROUP BY 1,2) pds
     >
     >
     >   where  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

When I ran above SQL I got the list of tables and '3.MVC', '2.SVC' and '1.NONE'. Could someone explain what MVC and SVC stand for and which tables require compression?

Thanks,

James Park
Database Specialist II
State of Maryland


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference