Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 09 Aug 2002 @ 18:15:21 GMT

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

Subj:   Re: Collect statistics
From:   John Hall

When statistics are collected on a column (or field), those statistics are stored in column FIELDSTATISTICS of table DBC.TVFIELDS. FWIW: There is one row in table DBC.TVFIELDS for each column of each table (including the system tables).

The column FIELDSTATISTICS is defined as a VARBYTE field (16383 bytes) and consists of a series of internal fields that contain the results of the COLLECT STATISTICS statement. One of those fields is a binary value giving the date/time for when the statistics were collected.

If FIELDSTATISTICS is NULL, then statistics have not been collected for that column.

The column LASTALTERTIMESTAMP was added with release of V2R3 (although I don't think that it actually worked until V2R4). When you COLLECT STATISTICS on a column, LASTALTERTIMESTAMP is updated to reflect the date/time of that COLLECT. So in that regard, you can use LASTALTERTIMESTAMP to determine the last time that statistics were collected on the given column.

The problem with depending on LASTALTERTIMESTAMP is that it also reflects when any change is made to the characteristics of the given column (for example, altering a column so that it's nullable).

Unless you have very tight control over changes to your tables, care has to be used with LASTALTERTIMESTAMP because it can't be depended upon to only contain the date/time of when statistics were collected.

Only the contents of column FIELDSTATISTICS is assured to have the correct date/time, but LASTALTERTIMESTAMP is much easier to use. It all depends on how much control you have over your environment.

This also applies to column INDEXSTATISTICS in table DBC.INDEXES.

  <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: 28 Jun 2020