Example: Object Use Counts for Tables - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
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.