Archives of the TeradataForum

Message Posted: Fri, 18 Dec 2004 @ 07:12:49 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.

     /* ############  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-17.
              Comments & feedback are welcome: [email protected] or [email protected].

     ################################################################## */
     SELECT  di.databasename, di.tablename, di.indexname, di.indextype,
        di.uniqueflag, max(columnposition) Nbr_columns,
        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,
        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, dbc.tablesize 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>>