Archives of the TeradataForum

Message Posted: Fri, 18 Dec 2004 @ 07:13:55 GMT

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

This query shows multicolumn NUSI's that do not match the PI of another table in the same database. This isn't necessarily a problem as the index may be needed for some queries. However, the more columns in the NUSI the less likely it will be used, and they NUSI's a signficant performance impact on data load and stats collection. You may find some obsolete NUSI's hanging around that were weak attempts to fix specific performance problems (often creating an index will get the users & operations off your back, even if it doesn't really help). With V2R5 collecting multicolumn statistics without an index may be a better solution..

     /* ############  NUSI NO MATCH PI ############
     Show NUSI not matching the PI on a different table.  May be unused if not
     covering specific queries.

     May be a candidate to drop, especially multicolumn nusi's.

     Author:  Terry Stover, Levi Strauss & Co.  2003-11-19.
              Comments & feedback are welcome: [email protected] or [email protected].

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


     SELECT  di.databasename, di.tablename, di.indexname, max(di.columnposition) nbr_cols,
        max (case when di.columnposition = 1 then di.columnname else '' end)  col1,
        max (case when di.columnposition = 2 then di.columnname else '' end)  col2,
        max (case when di.columnposition = 3 then di.columnname else '' end)  col3,
        max (case when di.columnposition = 4 then di.columnname else '' end)  col4,
        max (case when di.columnposition = 5 then di.columnname else '' end)  col5,
        max (case when di.columnposition = 6 then di.columnname else '' end)  col6,
        max (case when di.columnposition = 7 then di.columnname else '' end)  col7,
        max (case when di.columnposition = 8 then di.columnname else '' end)  col8,
        max (case when di.columnposition = 9 then di.columnname else '' end)  col9
     FROM
     dbc.indices di
     inner join
           ( select databasename, tablename, indexname, columnname
             from dbc.indices
             where indextype = 'S'  and uniqueflag = 'N' and databasename = 'dw_data'
           ) nusis
     on di.databasename = nusis.databasename and di.tablename = nusis.tablename
     and di.indexname = nusis.indexname
     left outer join
           ( select databasename, tablename, columnname
             from dbc.indices
             where indextype = 'P' and databasename = 'dw_data'
           ) pi
     on nusis.databasename = pi.databasename and nusis.tablename <> pi.tablename
     and nusis.columnname = pi.columnname
     WHERE pi.columnname is null
     GROUP BY di.databasename, di.tablename, di.indexname
     HAVING max(di.columnposition) > 2
     ORDER BY di.databasename, di.tablename, di.indexname



     --This variation includes a filter for minimum table size.

     --size basically a surrogate for row count.  Each NUSI is 8+ bytes / row in
     base table, 10+ bytes for PPI tables.

     --big fact tables with nusi's not matching PI's is the concern, not really
     concerned if small lookup/dimension type tables have unmatched nusi's


     SELECT  di.databasename, di.tablename, di.indexname, max(di.columnposition) nbr_cols,
        max (case when di.columnposition = 1 then di.columnname else '' end)  col1,
        max (case when di.columnposition = 2 then di.columnname else '' end)  col2,
        max (case when di.columnposition = 3 then di.columnname else '' end)  col3,
        max (case when di.columnposition = 4 then di.columnname else '' end)  col4,
        max (case when di.columnposition = 5 then di.columnname else '' end)  col5,
        max (case when di.columnposition = 6 then di.columnname else '' end)  col6,
        max (case when di.columnposition = 7 then di.columnname else '' end)  col7,
        max (case when di.columnposition = 8 then di.columnname else '' end)  col8,
        max (case when di.columnposition = 9 then di.columnname else '' end)  col9
     FROM
     dbc.indices di
     inner join
           ( select databasename, tablename, indexname, columnname
             from dbc.indices
             where indextype = 'S'  and uniqueflag = 'N' and databasename = 'dw_data'
           ) nusis
     on di.databasename = nusis.databasename and di.tablename = nusis.tablename
     and di.indexname = nusis.indexname
     inner join dbc.tablesize t
     on di.databasename = t.databasename and di.tablename = t.tablename
     left outer join
           ( select databasename, tablename, columnname
             from dbc.indices
             where indextype = 'P' and databasename = 'dw_data'
           ) pi
     on nusis.databasename = pi.databasename and nusis.tablename <> pi.tablename
     and nusis.columnname = pi.columnname
     WHERE pi.columnname is null
     GROUP BY di.databasename, di.tablename, di.indexname
     HAVING max(di.columnposition) > 2
        AND SUM(cast(t.currentperm as decimal(18,0))/(1024*1024)) > 10
     --assume at least 10mb to be a fact table
     ORDER BY di.databasename, di.tablename, di.indexname

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