September 27, 2003

select count(*) is not slow

a recent post I saw on comp.databases.oracle.server ....

From: Billy Verreynne

> Can someone tell me what's fast way to get total of records in a table
> except using "select count(*)" statement? Can I get the information from a
> system table?

Fast? You mean something like this:

SQL> set timing on
SQL> select count(*) from tjs_batch.prod_sapcallusage200304;

COUNT(*)
----------
78277166

Elapsed: 00:00:06.49
SQL>

This is a straight cut-and-paste from a telnet session. The 6 seconds
will go down to 4 or even 2 seconds when run again as the applicable
bitmap index used will be cached.

So what's your reason for not wanting to use SELECT COUNT(*) or
thinking its slow?

One thing that really makes me reach for my lead pipe, is Oracle urban
legends Andy. Like thinking a SELECT COUNT(*) is slow, that standard
Oracle database's uptime is much less than that of the server it runs
on, that Oracle requires constant administration and a host more or
other silly tales.

--
Billy

Posted by stu at September 27, 2003 09:08 PM