Different ways to count

scott@eddev> select count(*) from all_objects;

  COUNT(*)
----------
     23495

scott@eddev> select max(rownum) from all_objects;

MAX(ROWNUM)
-----------
      23495

scott@eddev> create table t
  2  as (select * from all_objects)
  3  /

Table created.

scott@eddev> select count(*) from t;

  COUNT(*)
----------
     23495

scott@eddev> select num_rows
  2  from user_tables
  3  where table_name = 'T';

  NUM_ROWS
----------

scott@eddev> analyze table T compute statistics;

Table analyzed.

scott@eddev> select num_rows
  2  from user_tables
  3  where table_name = 'T';

  NUM_ROWS
----------
     23495

Reader's Comments

1. Stu Lindenmayer | 29 Aug 2005 10:53 pm

   Hi Eddie, as you’ve mentioned selecting MAX(ROWNUM) from user_tables is the fastest method of counting rows. Although I’d advise that users should also select the LAST_ANALYSED column:

   select num_rows, 
   to_char(last_analyzed, 'DD-MON-YYYY HH24:MI') as valid_at 
   from dba_tables where table_name='EMP';
   
     NUM_ROWS VALID_AT
   ---------- -----------------
           14 30-AUG-2005 15:11
   

   This will provide the user with an idea of how old the row count acutally is.

   Additionally, I believe that a COUNT(rowid) will run a little faster than a COUNT(*) as validation of all rows is performed by Oracle for COUNT(*) operation.

   Also if an index exists on the table being counted, a COUNT of the indexed column will be quicker than a COUNT(rowid). For example, with an index on the emp table ename column:

   practice:SYS>explain plan for select count(rowid) from scott.emp;
   

   Explained.

   practice:SYS>@xpls
   
   Plan Table
   -----------------------------------------------------------------------------------------
   | Operation                           |  Name                   |  Rows | Bytes|  Cost  |
   -----------------------------------------------------------------------------------------
   | SELECT STATEMENT                    |                         |     1 |    7 |      2 |
   |  SORT AGGREGATE                     |                         |     1 |    7 |        |
   |   TABLE ACCESS FULL                 |EMP                      |    14 |   98 |      2 | 
   -----------------------------------------------------------------------------------------
   

   whereas hitting only the index column provides a faster count via a full index scan:

   explain plan for select count(ename) from scott.emp;

   Explained.

   practice:SYS>@xpls
   
   Plan Table
   -----------------------------------------------------------------------------------------
   | Operation                           |  Name                   |  Rows | Bytes|  Cost  |
   -----------------------------------------------------------------------------------------
   | SELECT STATEMENT                    |                         |     1 |    6 |      1 |
   |  SORT AGGREGATE                     |                         |     1 |    6 |        |
   |   INDEX FULL SCAN                   |EMP_IDX                  |    14 |   84 |      1 |
   -----------------------------------------------------------------------------------------
   

   Cheers!

2. Eddie Awad | 30 Aug 2005 12:19 pm

   Hi Stu,

   >as you’ve mentioned selecting MAX(ROWNUM) from user_tables is the fastest method of counting rows

   I think you meant “selecting num_rows from user_tables“.

   >I believe that a COUNT(rowid) will run a little faster than a COUNT(*)
   >a COUNT of the indexed column will be quicker than a COUNT(rowid)

   For more discussions and links about which form of count is faster, check my previous post about this subject.

   Thanks.

3. John Wimmer | 08 Sep 2005 12:45 pm

   If you take periodic exports, you can look at the export log and tell how many rows were exported. Depending on how big your output file is, it might be qquicker, plus is uses no database resources to find the number. Like the analyze number, it is dated, though. Handy of you don’t have good stats, but a current export.

4. Eddie Awad | 08 Sep 2005 5:23 pm

   Thanks for the tip John. It’s always good to have many alternatives to accomplish the same thing, so one can choose what best suits the need.