Archives of the TeradataForum

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

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

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>>