Archives of the TeradataForum

Message Posted: Fri, 09 Aug 2002 @ 11:21:53 GMT


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

Subj:		Re: Collect statistics

From:		Dieter N�th

Hi Axel

is there some way to show the last date of COLLECT STATISTICS on tables?

Yes

Help statistics makes problems, because it shows many lines per table. I want to generate a list of, for example, 5000 tables ordered by the date of the last collect statistics. Is there some dictionary table / view where I can see it?

SELECT
   d.DatabaseName
  ,t.TVMName AS TableName
  ,f.FieldName AS Name
  ,'C' AS "StatsType"
  ,f.LastAlterTimeStamp AS CollectTimeStamp
FROM dbc.TVFields f
JOIN dbc.TVM t ON f.tableid = t.tvmid
JOIN dbc.Dbase d ON t.DatabaseID = d.DatabaseID
WHERE
  f.FieldStatistics IS NOT NULL

UNION

SELECT
   d.DatabaseName
  ,t.TVMName AS TableName
  ,COALESCE(i.Name, 'Index ' || TRIM(i.IndexNumber) || ' ' || i.UniqueFlag || i.IndexType)
  ,'I'
  ,i.LastAlterTimeStamp
FROM dbc.Indexes i
JOIN dbc.TVM t ON i.tableid = t.tvmid
JOIN dbc.Dbase d ON t.DatabaseID = d.DatabaseID
WHERE
  i.IndexStatistics IS NOT NULL;

don't know if it's working in R3...

Dieter