COUNT ON index TABLES

Is this the fastest way to get a total count on a table with 30mil records:

select /*+ ROWID */ count(*) as "TOTAL" from
MINIAL.CHARGES

This query took 10 minutes!!!

It may be quicker to the count against an indexed column, if you have one e.g.

select count(primary_key_column) from minial.charges

That way, it should be able to get the data from the index without looking at the table.

Nonsense. Just "Select count(*) from MINIAL.CHARGES" and let Oracle work out the fastest way. If there's an appropriate index then it will be used.

Quote:

---

Originally Posted by slimdave

Nonsense. Just "Select count(*) from MINIAL.CHARGES" and let Oracle work out the fastest way. If there's an appropriate index then it will be used.

---

If only there were a silver bullet for select count??? :D :D :D :D :D :D

Too bad Oracle doesn't keep the count in the root block of the index. It would only need a few bytes, and getting the count would be a trivial matter of reading a field from the index root block.

Take slimdave's advice:
select count(*) is the best approach. (unless your using oracle 7 ).

CENSUSDBA ,

Do you have an index on a not null column?

Quote:

---

Originally Posted by ixion

Too bad Oracle doesn't keep the count in the root block of the index. It would only need a few bytes, and getting the count would be a trivial matter of reading a field from the index root block.

---

I can't imagine the contention on that block... :)

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.

But if you need the exact count, then follow slimdave's advice.

HTH & Regards,

rbaraer

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>

---

Quote:

---

Originally Posted by CENSUSDBA

Is this the fastest way to get a total count on a table with 30mil records:

select /*+ ROWID */ count(*) as "TOTAL" from
MINIAL.CHARGES

This query took 10 minutes!!!

---

whether you do count(*) or count(primary_key_column) the count timing difference is marginal.

Quote:

---

Originally Posted by dbasan

whether you do count(*) or count(primary_key_column) the count timing difference is marginal.

---

I would sat that it doesn't make any difference at all.

Quote:

---

Originally Posted by dbasan

whether you do count(*) or count(primary_key_column) the count timing difference is marginal.

---

Unless the PK is longer than another index, as might well be the case with a composite PK.

Oracle would prefer to fast full scan the smallest index, and if it's a choice between an index on a not-null varchar2(1) column and an index on a three-column primary key (or even a single-column date or numeric PK) then the smaller index will perform better.

Furthermore, counting the primary key column is not logically what you want, even if it guaranteed to give the same answer. What you want is the number of rows, and the appropriate syntax for that is count(*). Using count(pk_column) is an attempt to be tricky, just like using count(1), and to my mind that is also bad for easy understanding of the code.

use slimdave's suggestion + alter session to force parallel query.