Quote Originally Posted by RBARAER
Well, if an approximate count matches your needs, then you can just get NUM_ROWS from USER_TABLES or ALL_TABLES : it's the number of rows calculated by the last statistics gathering.
You might even use the SAMPLE clause to do that -- Oracle will still scan an index if available, but if not you'd only be reading 1% (or your chosen fraction) of the rows or blocks.

You don't want to "overcook" the sample percentage though.

Code:
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> drop table t
  2  /

Table dropped.

SQL>
SQL> create table t (col1 not null, col2)
  2  as
  3  select rownum, lpad(rownum,4000)
  4  from dual connect by level <= 10000
  5  /

Table created.

SQL>
SQL> set autotrace on
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
     10000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=16 Card=10000)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10034  consistent gets
       8051  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select 100*count(*) from t sample (1);

100*COUNT(*)
------------
        9600


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (SAMPLE) OF 'T' (TABLE) (Cost=16 Card=100 B
          ytes=700)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        476  consistent gets
       3081  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select 100*count(*) from t sample block (1);

100*COUNT(*)
------------
       12800


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (SAMPLE) OF 'T' (TABLE) (Cost=2 Card=100 By
          tes=700)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        162  consistent gets
         91  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> create index i on t (col1) compress;

Index created.

SQL>
SQL> exec dbms_stats.gather_index_stats(user,'i')

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
     10000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Card=10000
          )





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         33  consistent gets
         30  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select 100*count(*) from t sample (1);

100*COUNT(*)
------------
       10500


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (SAMPLE FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Car
          d=100 Bytes=700)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select 100*count(*) from t sample block (1);

100*COUNT(*)
------------
           0


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (SAMPLE FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Car
          d=100 Bytes=700)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>