Home Page for the TeradataForum
 

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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016