Re: counting rows

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 29 Nov 2000 07:02:33 +0100
Message-ID: <9026og$5vh0k$4@ID-62141.news.dfncis.de>

"Aaron D. Hager" <adhager_at_onebox.com> wrote in message news:90250b$1jj$1_at_newsmaster.cc.columbia.edu...
> Does anyone know a faster way of counting the number of rows in a table
> (using OCI or SQL) other than
> SELECT COUNT (*) FROM table
> ?
>
> Thanks,
>
> Aaron
>
>

As you provide no clues as to which platform you might be running, it is impossible to come up with a specific answer. Generally speaking select count(*) is the only method to get *correct,adequate* results. Someone may advise to use num_rows from the datadictionary, but that would only be valid directly following an analyze compute statistics, as analyze estimate estimates num_rows too.
If your tables are indexed you could consider adding a dummy where clause on the index, as that will force the Cost Based Optimizer to use an index fast full scan on the table (so without reading the table).

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Nov 29 2000 - 00:02:33 CST