Question:

Is there any other way than "select count(*) from <table>" to return the
number of rows in an Oracle table?  This takes forever to run (>3
minutes) on a table that has over 3 million rows.  I guess it has to do
a full table scan to determine the correct number of rows and in a read
consistent manner.  It would be OK even if we got a count that was
fairly close.

Any Ideas or Insight?

Answer 1:

1) select max(rownum) from table;

or

2) select count( <indexed_column> ) from table

Answer 2:

You could put a bitmap index on it - the count(*) will then fly along
although a whole lot of other things may come into play.

Answer 3:

If you do an "analyze table XXX estimate statistics" every night or
even twice a day on the table in question, you could do:

select num_rows from user_tables where table_name = 'XXX';

That would return the approximate row count as of the last analyze
(stored in the Last_Analyzed column in the same dictionary view,
User_Tables).

Answer 4:

If there is an index on the table, instruct oracle to count the nodes in
the index by providing a hint:

select /*+ index(table_name) */
count(*) from table_name;

Answer 5:

Depending on your hardware, you may get some benefit from enabling
parallelism - i.e

SQL> select count(*) from sale_orders;

  COUNT(*)
----------
    277500

Elapsed: 00:00:00.91

SQL> alter table sale_orders parallel 8;

Table altered.

Elapsed: 00:00:00.40

SQL> select count(*) from sale_orders;

  COUNT(*)
----------
    277500

Elapsed: 00:00:00.30

Note that this is on a single CPU, single drive NT laptop. On a machine
with multiple CPU's, and sufficient I/O bandwidth, this can really fly.

If you don't want an exact number, in Oracle8i you could try sampling
i.e

SQL> select count(*)*10 from sale_orders sample(10) block;

COUNT(*)*10
-----------
     279010

Elapsed: 00:00:00.31

Alternatively, if statistics are reasonably up-to-date, you could just
use the number of rows counted last time statistics were collected, i.e

SQL> select table_name,num_rows from user_tables where table_name =
'SALE_ORDERS';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SALE_ORDERS                        274603

Elapsed: 00:00:00.80

--
Regards,
 

Mark Townsend