Archives of the TeradataForum

Message Posted: Wed, 22 Dec 2004 @ 17:34:53 GMT

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

Another useful DD query, displays one row for each table, shows table skew & variance along with PI columns. Thanks to Ulrich Arndt for pointing out product join in the original verision.

     /* ############  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>>