Example: Object Use Counts for Tables - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

Assume two databases, db1 and db2, each contain some tables and other objects. Assume also that count collection is enabled.

Now specify some SQL requests to access the tables in the two databases.

sel *from db1.tab1;
sel *from db1.tab1 where col1=7;
sel *from db1.tab1 where col2=7;
sel *from db1.tab2;
sel *from db2.tab3;
sel *from db2.tab3 where col1=7;
sel *from db2.tab4;

Then use the Data Dictionary view TablesVX to find the use counts for the tables.

sel DatabaseName, TableName, AccessCount, LastAccessTimeStamp
from DBC.TablesVX where TableName IN ('tab1', 'tab2', 'tab3', 'tab4');

The output is:

 *** Query completed. 4 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
DatabaseName  db1
          TableName  tab2
        AccessCount  1
LastAccessTimeStamp  2006-12-17 14:51:53
       DatabaseName  db2
          TableName  tab4
        AccessCount  1
       DatabaseName  db1
LastAccessTimeStamp  2006-12-17 14:51:53
          TableName  tab1
        AccessCount  3
LastAccessTimeStamp  2006-12-17 14:51:53
       DatabaseName  db2
          TableName  tab3
        AccessCount  2
LastAccessTimeStamp  2006-12-17 14:51:53
=============================================================
If any of the named objects have not been accessed since the last use count reset, the value in the AccessCount column for that object appears as zero, and the LastAccessTimeStamp will appear as a question mark.