Archives of the TeradataForum

Message Posted: Fri, 28 Aug 2003 @ 22:44:46 GMT

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

Pretty much there are only three ways to understand how much a table has been changed:

1. understanding the nature of access and making some sort of an estimate.

2. looking at the actual queries run against that table or

3. comparing it to a baseline copy.

Presumably you don't want to keep a baseline copy which is reset each time you collect statistics so that option is crossed off the list.

In current versions of Teradata the only way to look at the actual queries run against a table (that I know of) is to use access logging. When access logging is enabled, you can ask Teradata to record certain queries in a log table (dbc.accesslog). The "Begin logging" query is used to tell it what to record.

You could then analyse the dbc.accesslog table for hits against the tables that you wish to track changes against. You can filter on the accesstype column (D = delete, I = insert etc) OR THE statementtype column. From memory, the TVMName column contains the target table of the Operation.

Would I set up accesslogging to monitor when stats need to be refreshed? Probably not, as other posts have advised you only need to refresh statistics if a certain percentage of the rows have changed. And even then only if that nature of the changes would alter the demographics of the statistics previously collected. For example, if you update some ones name 5,000 times and the name column is not an index column, then refreshing statistics probably isn't going to do anything for you. Indeed even if the name was a PI column and the only change was 5,000 updates to this one record (out of say 1,000 records) then you still probably wouldn't need to refresh stats because there has been almost no change to the demographics of the table (1 record out of 1,000 has been rehashed and redistributed) - but that is a whole 'nother topic.

What I am trying to say is that even if you set up and monitor the access log it still might not provide you with the information you are seeking.

You might be better off for now simply refreshing your statistics every night, or once a week or whatever depending upon how the table is used. That is option 1 from the above list.

If you do choose to go down the access logging path:

NOTE: You have to install a module into the RDBMS to enable the logging capability.

BEWARE: If you install the module there will be a performance degradation (even if you do nothing else). Refer to the manuals for details on setting up access logging.

Hope this helps

Glenn Mc

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