Archives of the TeradataForum

Message Posted: Mon, 31 Oct 2005 @ 19:41:47 GMT

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

Here's a query I use to review skew at the db level. Note that you have to change the databasename in the query. This is more of a dba query, you may not have access to the dbc tables.

     /* ##################  PI COLUMNS CROSSTAB WITH TABLE SKEW ##################
     Show tablename, perm space MBytes, skew, PI type and PI columns
     (crosstab), index and table access counts.
     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")
     Note, You must change the databasename filter (currently 'dw_data') in 2
     places

     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.
     2005-03-17  Added table and index access counts for V2R5.  Also
     concatenated index column names into one query output column.

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

     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(di.accesscount) idx_access,
              tabs.accesscount tbl_access,
         max(case when columnposition = 1 then trim(columnname) else '' end)
      || max(case when columnposition = 2 then ', ' || trim(columnname) else '' end)
      || max(case when columnposition = 3 then ', ' || trim(columnname) else '' end)
      || max(case when columnposition = 4 then ', ' || trim(columnname) else '' end)
      || max(case when columnposition = 5 then ', ' || trim(columnname) else '' end)
      || max(case when columnposition = 6 then ', ' || trim(columnname) else '' end)
      || max(case when columnposition = 7 then ', ' || trim(columnname) else '' end)
      || max(case when columnposition = 8 then ', ' || trim(columnname) else '' end) indexcolumns

     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 tmp,
          (select databasename, tablename, accesscount from dbc.tables where databasename in ('dw_data')) tabs

     WHERE  di.databasename in ('dw_data')
             and di.indextype = 'P'
             and di.databasename = tmp.databasename
             and di.tablename = tmp.tablename
             and di.databasename = tabs.databasename
             and di.tablename = tabs.tablename
     GROUP BY di.databasename,
              di.tablename,
              di.indextype,
              di.indexname,
              di.uniqueflag,
              tabs.accesscount
     ORDER BY di.databasename,
              di.tablename,
              di.indextype,
              di.indexname

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