Archives of the TeradataForum

Message Posted: Thu, 18 Mar 2005 @ 01:58:44 GMT

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

Here's an updated version of a query for reviewing PI's. I added the PI and table access counts for V2R5 and collapsed the PI columns into a single output column, comma separated. The

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

     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) pi_accesscount,
              tabs.accesscount tbl_accesscount,
             cast(max(di.accesscount) as
     decimal(9,4))/nullifzero(cast(tabs.accesscount as decimal(9,4))) pi_rate,
         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>>