Quicktips – Rowcount

Quick rowcount

Sometimes we are interested in how many records are in a table.
It is easy, we issue the typical rowcount SQL statement:

select count(*) from <table>;

However Teradata can serve this request effectively by only scanning the cylinder index (not all data blocks), it still can last for a long time in case of big tables and extensively loaded systems.

One can have a quite good assumption for the record count from the statistics. This method is implemented in the PRISE Tuning Assistant software

V13.10 and before

help stat <table>;

Choose any existing statistics, the latest is the best (by “Date”;”Time”), and then issue this, replacing “xxx” with the chosen stat colmn(s):

help stat <table> column (xxx);

The “Number of rows” column shows the estimated record count. The same value appears for each buckets, take the value in the first row.

V14.00 and after

select databasename, tablename, RowCount,LastCollectTimestamp from dbc.statsv
where databasename='<db>' and tablename='<table>'
qualify sum(1) over (partition by databasename,tablename order by lastcollecttimestamp desc rows unbounded preceding) = 1
;

Please note that this data may be an estimation (sample statistics) or outdated (stale statistics).

Without statistics

In this case we can have deduct the order of magnitude from the allocated size of the table.
Naturally, the recordcount strongly depends on the average record size.

select databasename,tablename, sum(currentperm) /1024/1024 Table_MBytes
from dbc.allspaceV
where databasename='<db>' and tablename = '<table>'
group by 1,2;