Archives of the TeradataForum

Message Posted: Wed, 13 Oct 2004 @ 13:00:01 GMT

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

Try this. A most excellent contractor left this with us. It needs to be tweaked for Multi Column Stats statements.

     create view dbc.v_db_tbl_idx_isstat as
      locking DBC.indexes for access
      locking DBC.dbase for access
      locking DBC.tvm for access
      locking DBC.tvfields for access
      SELECT dbase.DatabaseID,
                tvm.TVMId,
                dbase.DatabaseName,
                tvm.TVMName as TableName,
                indexes.IndexNumber(FORMAT 'ZZ9'),
                indexes.Name (NAMED IndexName),
                case when indexes.IndexStatistics is null then 0 else 1 end
     as IsStat
      FROM DBC.indexes, DBC.dbase, DBC.tvm, DBC.tvfields
      WHERE   tvm.DatabaseId = dbase.DatabaseId
      AND     tvm.tvmid = indexes.tableid
      AND     tvm.tvmid = tvfields.tableid
      AND     tvfields.fieldid = indexes.fieldid
      group by 1,2,3,4,5,6,indexes.indexstatistics
      ;

     create view dbc.v_db_tbl_col_isstat as
      locking DBC.dbase for access
      locking DBC.tvm for access
      locking DBC.tvfields for access
      SELECT dbase.DatabaseID,
                tvm.TVMId,
                dbase.DatabaseName,
                tvm.TVMName as TableName,
                tvfields.fieldid as columnid,
                tvfields.fieldname (NAMED ColumnName),
                case when tvfields.fieldstatistics is null then 0 else 1 end
     as IsStat
      FROM DBC.dbase, DBC.tvm, DBC.tvfields
      WHERE   tvm.DatabaseId = dbase.DatabaseId
      AND     tvm.tvmid = tvfields.tableid
      group by 1,2,3,4,5,6,tvfields.fieldstatistics
      ;

     replace macro dbc.generate_collect_stmts (dbname char(30)) as (
      select  'collect statistics on ' || trim(databasename) || '.' ||
     trim(tablename)
      || ' column ' || trim(columnname) || ';' (title '')
      from dbc.v_DB_TBL_COL_IsStat ISS
      where databasename = :dbname
        and isstat       = 1
      order by tablename, columnid;
      select 'collect statistics on ' || trim(IDX.databasename) || '.' ||
     trim(IDX.tablename) || ' index ('
      || trim(max(case when IDX.columnposition = 1 then IDX.columnname else
     NULL end))
      || trim(max(case when IDX.columnposition = 2 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 3 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 4 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 5 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 6 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 7 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 8 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 9 then (',' ||
     IDX.columnname) else ' ' end))
      || trim(max(case when IDX.columnposition = 10 then (',' ||
     IDX.columnname) else ' ' end))
      || ');' (Title '')
       from dbc.indices IDX, dbc.v_DB_TBL_IDX_IsStat ISS
      where ISS.databasename = IDX.databasename
        and ISS.tablename    = IDX.tablename
        and ISS.indexnumber  = IDX.indexnumber
        and ISS.databasename = :dbname
        and ISS.ISSTAT       = 1
      group by IDX.databasename, IDX.tablename, IDX.indexnumber
      order by idx.tablename,idx.indexnumber;);

     Exec dbc.generate_collect_stmts ('databasename');

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