In Oracle, there are at least three different ways to count the number of records a SQL query returns:
COUNT(*)MAX(ROWNUM)- Column
num_rowsinuser_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:
- COUNT(*) vs COUNT(1)
- Top 10 Ways to Know Your Software Project is Doomed
- Here is How to Unpersist Your Persistent PL/SQL Package Data
- SQL PLUS Gotcha
- links for 2006-09-15
Tagged count, sql | Post a Comment


















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!
August 29th, 2005, at 10:53 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](http://awads.net/wp/2005/07/06/count-vs-count1/) about this subject.
Thanks.
August 30th, 2005, at 12:19 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.
September 8th, 2005, at 12:45 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.
September 8th, 2005, at 5:23 pm #