Different ways to count

In Oracle, there are at least three different ways to count the number of records a SQL query returns:

  1. COUNT(*)
  2. MAX(ROWNUM)
  3. Column num_rows in user_tables

Using COUNT(*) is the most common way:

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

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

Using MAX(ROWNUM) returns the same result:

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

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

There is a third way, querying the data dictionary. However, in order to use this option and have meaningful results, you need to analyze the table (compute statistics) before querying the data dictionary. Here is an example:

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
----------

As you can see (or not see), the column is empty (or null); That’s because the table T is not analyzed. Let’s analyze it:

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

Now the column returns the correct number of rows. Note, however, that if there are a lot of inserts/deletes into/from the table, querying user_tables may not be a good choice. This approach works best for static (or near-static) tables that are frequently analyzed.

Based on my quick tests, I found out that the slowest way to count records (especially on large tables) is to use MAX(ROWNUM) and the fastest way is to select num_rows from user_tables. Then again, in many situations selecting from user_tables is not a good choice.

Possibly related:

Tagged , | Post a Comment