Alternative to count(*) in Teradata

Database
N/A

Alternative to count(*) in Teradata

Hello All,
Is there an alternative to count(*) in Teradata? Is the row count stored in any dbc tables?
I have found there is alternatives in MS SQL Server :
Select rows from sysindexes
where Indid = 2 and Id = (Select object_id(''))

I would like to know if there is the same for TD12.
5 REPLIES
Senior Apprentice

Re: Alternative to count(*) in Teradata

No.
But the table's statistics will show the row count from the time when the stats where collected. Thus this is only approximate, but afaik the count returned by SQL Server is not guaranteed to be accurate, too.

Btw, in TD13 a COUNT(*) is much faster than before.

Dieter
Teradata Employee

Re: Alternative to count(*) in Teradata

Another way: if the table has a USI and collected statistics on that index, you can do a HELP STATS DATABASENAME.TABLENAME and look at Unique Value column.
Remember: It is not a 100% value (you need fresh statistics!).
Teradata Employee

Re: Alternative to count(*) in Teradata

@dnoeth: I would like to know why in TD13, count(*) runs much faster than before.
Thanks.
Senior Apprentice

Re: Alternative to count(*) in Teradata

It's faster because it's no longer doing a Full Table Scan.
Instead of reading all datablocks only the cylinder indexes are accessed (which include the number of rows per datablock).
Of course this is only possible for a COUNT(*) or COUNT(non-null column) without any WHERE-condition.

The same technique was already implemented before TD13 for a fast-path DELETE without WHERE-condition.

Dieter
Teradata Employee

Re: Alternative to count(*) in Teradata

The storing of row-count in SQL Server is totally different .... whenever the rows are inserted or deleted it maintains the count in its system-tables. This makes additional work to be done i.e. more I/Os, more data to be processed and more rows to be updated.

Now, imagine that being done against a few GBs of data coming in everyday in a warehouse .... that is a big overhead .... that is the reason Teradata doesn't do it.

HTH!

Regards,

MAC