17.00 - 17.05 - Usage Recommendations - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Advanced SQL Engine
Teradata Database
Release Number
June 2020
English (United States)
Last Update
When collecting object use counts, remember the following:
  • Do not enable collection when a Data Dictionary ARCHIVE or RESTORE is in progress.
    If the Data Dictionary is being archived and the archive gets blocked by the ObjectUseCount collection, the system will turn off the ObjectUseCount collection. You must enable it again after the archive is complete.
  • The recommended value of ObjectUseCountCollectRate is 10 minutes or more. Setting ObjectUseCountCollectRate to less than 10 minutes impacts system performance.

Copying the Use Counts of Table

CREATE TABLE AS...WITH DATA AND STATISTICS copies the statistics and data of an original table to a target table. If the USECOUNT option is enabled on the target database, the AND STATISTICS option also copies the use count information. For more information, see Copying Statistics From a Base to a Target Table or Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 , CREATE TABLE (AS Clause).

Renaming Objects Has No Effect on Use Counts

Renaming an object does not reset its use count. That is, neither the access count nor the last access timestamp changes when you rename an object. For example, the following statement:


does not reset the use count for the table “Emp” to 0. The count remains what it was for “Employee” with the timestamp it was last used. When “Emp” is next used, the system continues the tally and updates the timestamp. Similarly, for columns of a table, if you rename a column using the following request:

ALTER TABLE t1 RENAME  oldcolumnname  TO  newcolumnname;

this does not reset the use count information for the column.

Because using the RENAME statement does not affect use count information, if you need to reset the count, manually reset it using the ClearTVMUseCount macro.