Archives of the TeradataForum

Message Posted: Thu, 18 Mar 2005 @ 02:06:19 GMT

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

Here's an updated version of a query for reviewing SI's. I added the index and table access counts for V2R5 and collapsed the PI columns into a single output column, comma separated. NUSI's use 8+ bytes per row, so the NUSI vs table hit rates should be high enough to justify the space & load time.

     /* ############  CROSSTAB OF INDEX COLUMNS PI&SI  ############
     Shows databasename, tablename, index name & type, number of columns and
     column list.
     good for a quick review of existing indexes.
     Note that NUSI's take 8+ bytes per row and slow data loads.  Serious
     consideration of the NUSI cost/benefit ratio is required.
      We dropped 3 of 4 nusi's on a large table (2 billion rows, 200 Gig),
     and the load rate (multiload) increased by 3x,
      which eliminated the weekly ETL bottleneck.

     Author:  Terry Stover, Levi Strauss & Co.  2003-09-14.
      Comments & feedback are welcome: tstover@levi.com
     <>  or terry.stover@comcast.net
     <> .

     2005-03-17  Added table and index accesscounts for V2R5.  Also
     concatenated index column names into output column.
     ################################################################## */

     SELECT  di.databasename, di.tablename, di.indexname, di.indextype, di.uniqueflag,
      max(di.accesscount) idx_accesscount,
      tabs.accesscount tbl_accesscount,
             cast(max(di.accesscount) as
     decimal(9,4))/nullifzero(cast(tabs.accesscount as decimal(9,4))) idx_rate,
      max(columnposition) nbr_cols,
      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, accesscount from dbc.tables where databasename in ('dw_data')) tabs
     WHERE di.databasename in ('dw_data')
      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


     /*  Old version 4.1.3
     SELECT  di.databasename, di.tablename, di.indexname, di.indextype,
     di.uniqueflag, max(columnposition) nbr_cols,
      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
     WHERE di.databasename in ('dw_data')
     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>>