Archives of the TeradataForum

Message Posted: Thu, 06 Jan 2005 @ 17:36:44 GMT

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

I haven't run into any discrepancies other than rounding between the skew values in Teradata administrator and the query. You do have an old version of the skew query that doesn't properly handle the table size for tables with multi column PI's (the skew and variance values were still accurate though). Here's the corrected version.

     /* ############  PI COLUMNS CROSSTAB WITH TABLE SKEW  ############
     Show tablename, perm space MBytes, skew, PI type and PI columns (crosstab).

     Useful for a quick overview of your PI's.  One goal is to to keep row
     variance < 5%
     (reference V2R5.1 DB Design Manual, p 10-32, "Analyzing Column Distribution
     Demographics to Optimize PI Selection")


     Author:  Terry Stover, Levi Strauss & Co.  2004-05-14.
              Comments & feedback are welcome: [email protected] or [email protected].
     Change History
     2004-12-22  Corrected product join between dbc.tables and dbc.indices. Was
     giving incorrect total space for multi column PI. Feedback by Ulrich Arndt.

     ############################################################## */


     lock row for access
     SELECT di.databasename,
              di.tablename,
              di.indexname,
              di.indextype,
              di.uniqueflag,
              max(columnposition) Nbr_columns,
              max(perm_mb) perm_mb,
              max(tableskew) as tableskew,
              max(tablevariance) as tablevariance,
              max (case when columnposition = 1 then columnname else '' end) col1,
              max (case when columnposition = 2 then columnname else '' end) col2,
              max (case when columnposition = 3 then columnname else '' end) col3,
              max (case when columnposition = 4 then columnname else '' end) col4,
              max (case when columnposition = 5 then columnname else '' end) col5,
              max (case when columnposition = 6 then columnname else '' end) col6,
              max (case when columnposition = 7 then columnname else '' end) col7,
              max (case when columnposition = 8 then columnname else '' end) col8,
              max (case when columnposition = 9 then columnname else '' end) col9
     FROM dbc.indices di,
           (select databasename,
                   tablename,
                   SUM(cast(t.currentperm as decimal(18,0))/(1024*1024)) perm_mb,
                   100*((cast(max(t.currentperm ) as decimal(18,3)) -
     cast(ave(t.currentperm ) as decimal(18,3))))
        /(nullif(cast(max(t.currentperm) as decimal(18,3)),0)) as tableskew,
           100*((cast(max(t.currentperm ) as decimal(18,3)) -
     cast(min(t.currentperm ) as decimal(18,3))))
        /(nullif(cast(min(t.currentperm) as decimal(18,3)),0) )
     as tablevariance
            from dbc.tablesize t
            where databasename in ('dw_data')
            group by databasename, tablename
           ) as t
     WHERE  di.databasename in ('dw_data')
             and di.indextype = 'P'
     and di.databasename = t.databasename
     and di.tablename = t.tablename
     GROUP BY di.databasename,
              di.tablename,
              di.indextype,
              di.indexname,
              di.uniqueflag
     ORDER BY di.databasename,
              di.tablename,
              di.indextype,
              di.indexname

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