Archives of the TeradataForum

Message Posted: Thu, 07 Apr 2005 @ 17:34:31 GMT

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

I added Dieter's row counts from stats logic to my PI overview query, uses stats from the PI to calculate row counts.

     /* ############  PI COLUMNS CROSSTAB WITH TABLE SKEW  ############
     Show tablename, row count, perm space MBytes, skew, PI type and PI
     columns (crosstab), index and table access counts.

     Useful for a quick overview of your PI's.
     Primary PI criteria is access paths, use PI vs table access count to see
     how you're doing.   Another 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.
     2005-03-17  Added table and index access counts for V2R5.  Also
     concatenated index column names into a single output column.
     2005-04-07  Added row counts from PI stats to query.  Row counts from
     stats logic by Dieter Noeth.
     ################################################################## */

     lock row for access
     SELECT idx.databasename,
             idx.tablename,
             idx.indextype,
             idx.uniqueflag,
             idx.PI_Column_Count,
             cast(tabrows.NumRows as decimal(18,3)) / 1000 K_Rows,
             tabsize.perm_mb,
             tabsize.tableskew,
             tabsize.tablevariance,
             idx.indexcolumns,
             idx.accesscount pi_accesscount,
             tabhits.accesscount tbl_accesscount,
             cast(idx.accesscount as decimal(9,4))/nullifzero(cast(tabhits.accesscount as decimal(9,4))) pi_rate
     FROM
     (
        SELECT databasename, tablename, indexname,indexnumber, indextype,
     uniqueflag, max(columnposition) PI_Column_Count, max(accesscount) as accesscount,
          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
      WHERE indextype = 'P'
      GROUP BY databasename, tablename, indexname,indexnumber, indextype, uniqueflag) as idx,
     (
       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
      GROUP BY databasename, tablename) as tabsize,
     (
       SELECT databasename, tablename, accesscount from dbc.tables) 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  idx.databasename in ('edward_data')
      and idx.databasename = tabsize.databasename
      and idx.tablename = tabsize.tablename
      and idx.databasename = tabhits.databasename
      and idx.tablename = tabhits.tablename
      and idx.databasename = tabrows.databasename
      and idx.tablename = tabrows.tablename
     ORDER BY idx.databasename,
              idx.tablename

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