Archives of the TeradataForum

Message Posted: Mon, 13 Mar 2006 @ 20:00:11 GMT

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

Here's a query I use for reviewing PI selection, it includes a derived table that calculates row counts from the PI statistics. Stats to row count logic provided by Dieter Noeth.

     /* ###########  PI COLUMNS CROSSTAB WITH TABLE SKEW  ###########
     Show tablename, perm space MBytes, row count from PI stats, skew, PI
     type and PI columns (crosstab), PI 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 3
     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.
     2005-04-07  Added row counts from stats.  Rows from stats logic from
     Dieter Noeth.
     ############################################################### */

     lock row for access
     SELECT di.databasename,
         di.tablename,
         di.indexname,
         di.indextype,
         di.uniqueflag,
         MAX(di.columnposition) Nbr_columns,
         MAX(tabrows.NumRows)/1000 table_Krows,
         MAX(tabskew.perm_mb) perm_mb,
         MAX(tabskew.tableskew) AS tableskew,
         MAX(tabskew.tablevariance) AS tablevariance,
         MAX(di.accesscount) idx_access,
         MAX(tabhits.accesscount) tbl_access,
         MAX(CASE WHEN di.columnposition = 1 THEN trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.columnposition = 2 THEN ', ' || trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.columnposition = 3 THEN ', ' || trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.columnposition = 4 THEN ', ' || trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.columnposition = 5 THEN ', ' || trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.columnposition = 6 THEN ', ' || trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.columnposition = 7 THEN ', ' || trim(columnname) ELSE '' END)
      || MAX(CASE WHEN di.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 tabskew,
     (SELECT databasename, tablename, accesscount FROM dbc.tables WHERE
     databasename IN ('dw_data')) AS tabhits,
     (SELECT databasename, tablename,
         CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END AS Offset,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+7, 1)
                  || SUBSTR(Stats, 57+Offset+6, 1) (BYTE(4))) AS NumRowsw1,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+5, 1)
                  || SUBSTR(Stats, 57+Offset+4, 1) (BYTE(4))) AS NumRowsw2,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+3, 1)
                  || SUBSTR(Stats, 57+Offset+2, 1) (BYTE(4))) AS NumRowsw3,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+1, 1)
                  || SUBSTR(Stats, 57+Offset+0, 1) (BYTE(4))) AS NumRowsw4,
          (-1**(NumRowsw1 / 32768))
          * (2**((NumRowsw1/16 MOD 2048) - 1023))
          * (1 + ((NumRowsw1 MOD 16) * 2**-4) + (NumRowsw2 * 2**-20)
               + (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52)) (DECIMAL(18,0))
     AS NumRows
        FROM
        (SELECT d.databasename AS DatabaseName,
             t.tvmname AS TableName,
             SUBSTR(i.IndexStatistics, 1, 80) AS Stats
           FROM
             dbc.dbase d
           JOIN dbc.tvm t
             ON d.databaseid = t.databaseid
           JOIN dbc.tvfields c
             ON t.tvmid = c.tableid
           JOIN dbc.Indexes i
             ON c.tableid = i.tableid
             AND c.fieldid = i.fieldid
           WHERE i.indextype = 'P'
           GROUP BY DatabaseName, TableName, i.IndexStatistics
           HAVING Stats IS NOT NULL
       ) statssub
     ) AS tabrows
     WHERE  di.databasename IN ('dw_data')
         AND di.indextype = 'P'
         AND di.databasename = tabskew.databasename
         AND di.tablename = tabskew.tablename
         AND di.databasename = tabhits.databasename
         AND di.tablename = tabhits.tablename
         AND di.databasename = tabrows.databasename
         AND di.tablename = tabrows.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>>