Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023