Archives of the TeradataForum

Message Posted: Fri, 24 Mar 2006 @ 20:39:10 GMT

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

<-- Anonymously Posted: Friday, March 24, 2006 15:45 -->

Andrea's question...

Answer...

Here is an example of what we used to refresh table using 10% change within a week as guide.

     .os cat /dev/null > $OUT/idw_collect_stats_daily.btq
     .export file $OUT/idw_collect_stats_daily.btq

     sel
     distinct 'collect stats on '||
     trim(a.databasename)||'.'||trim(a.tablename)||' ;' (title '')
     from
     (select logdate, databasename, tablename, sum(currentperm) tablesize
     from shadow.tablesize
     group by 1,2,3) a
     join
     (select date as logdate, databasename, tablename, sum(currentperm)
     tablesize
     from dbc.tablesize
     group by 1,2,3) b
     on a.databasename=b.databasename
     and a.tablename=b.tablename
     and a.logdate+1=b.logdate
     where
     exists
     (select 1
     from test1.idw_production_tables
     where databasename=a.databasename
     and tablename=a.tablename)
     and b.logdate=date
     and (b.tablesize-a.tablesize)/b.tablesize > 0.0142
     order by a.databasename, a.tablename
     ;
     .if errorcode <> 0 then .exit errorcode

     .export reset
     .run file=$OUT/idw_collect_stats_daily.btq

Here is an example what we do to collect stats on primary index when a table is brand new to the warehouse and stats was not defined.

     .os cat /dev/null> $OUT/idw_collect_stats_new.btq
     .export file $OUT/idw_collect_stats_new.btq

     sel
     (case when a.columnposition=b.min_column
     and a.columnposition<>b.max_column
     then 'collect stats on '||trim(a.databasename)||
     '.'||trim(a.tablename)||
     ' column  ('||trim(a.columnname)
     when a.columnposition=b.min_column
     and a.columnposition=b.max_column
     then 'collect stats on '||trim(a.databasename)||
     '.'||trim(a.tablename)||
     ' column  ('||trim(a.columnname)||' ) ;'
     when a.columnposition=b.max_column
     and a.columnposition<>b.min_column
     then ' , '||trim(a.columnname)||' ) ;'
     else ' ,'||trim(a.columnname) end) (title '')
     from dbc.indices a
     join
     (select databasename, tablename,
     min(columnposition) as min_column, max(columnposition) as max_column
     from dbc.indices I
     where indextype in ('p','q')
     group by 1,2) b
     on a.databasename=b.databasename
     and a.tablename=b.tablename
     where a.indextype in ('p','q')
     and exists
     (select 1
     from test1.idw_production_tables
     where
     databasename=a.databasename
     and tablename=a.tablename)
     and exists
     (select 1
     from dbc.tables
     where
     createtimestamp > current_timestamp - interval '3' day
     and databasename=a.databasename
     and tablename=a.tablename)
     order by a.databasename, a.tablename, a.columnposition
     ;
     .if errorcode <> 0 then .exit errorcode

     sel
     'collect stats on '||trim(a.databasename)||
     '.'||trim(a.tablename)||
     ' column  ('||trim(a.columnname)||' ) ;' (title '')
     from dbc.indices a
     where a.indextype in ('p','q')
     and exists
     (select 1
     from test1.idw_production_tables
     where
     databasename=a.databasename
     and tablename=a.tablename)
     and (databasename, tablename) in
     (select databasename, tablename
     from dbc.tables
     where
     createtimestamp > current_timestamp - interval '3' day)
     order by a.databasename, a.tablename, a.columnposition
     ;
     .if errorcode <> 0 then .exit errorcode
     .export reset

     .run file=$OUT/idw_collect_stats_new.btq

There are other write up in the past from some very smart people on how to look at stats information with the system and determine when was the last time the stats was collected then react to it. You should be able to find them in the archive area of this forum.

Best wish.

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