All Forums Database
echoo 1 post Joined 03/10
16 Mar 2010
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.

dnoeth 4628 posts Joined 11/04
17 Mar 2010

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

Dieter

rboaretto 4 posts Joined 07/09
18 Mar 2010

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

amit.s 8 posts Joined 03/10
20 Mar 2010

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

dnoeth 4628 posts Joined 11/04
21 Mar 2010

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

Dieter

Adeel Chaudhry 773 posts Joined 04/08
01 Apr 2010

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.