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.
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.
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!).
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.
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.