Faster alternative in Oracle to SELECT COUNT(*) FROM sometable

Question

I've notice that in Oracle, the query

SELECT COUNT(*) FROM sometable;

is very slow for large tables. It seems like the database it actually going through every row and incrementing a counter one at a time. I would think that there would be a counter somewhere in the table how many rows that table has.

So if I want to check the number of rows in a table in Oracle, what is the fastest way to do that?

For what purpose are you checking the number of rows? Is this something you do ad hoc e.g. for your own purposes in SQLDeveloper or SQL*PLUS, or is this going to be part of a program, e.g. get the number of rows so you can display "page 1 of 345" or something? Do you need the exact count, or will an approximation work as well? — shoover, Dec 03 '09 at 19:06
BTW, I'm thinking if you just need an approximation, and this is something you do ad hoc, then if your tables are analyzed recently, you can SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'SOME_TABLE'; — shoover, Dec 03 '09 at 19:29
Thanks, that does exactly what I need, since as you guessed I'm only usually looking for an approximation. — Eli Courtwright, Jan 07 '10 at 22:18

score 70 · Answer 1 · edited Dec 01 '16 at 02:53

70

This works great for large tables.

SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'TABLE_NAME_IN_UPPERCASE';

For small to medium size tables, following will be ok.

SELECT COUNT(Primary_Key) FROM table_name;

Cheers,

edited Dec 01 '16 at 02:53

rationalboss

5,330
3
30
50

answered Feb 04 '13 at 16:32

AMISH G SHAH

709
1
5
2

6

Make sure `table_name` is in the form `'TABLE_NAME'` – imanuelcostigan Feb 15 '13 at 02:08
14

This isn't updated in real time, and could be not updated at all depending on your database. http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2105.htm#REFRN20286 – Joseph Dec 10 '13 at 18:19
For a table with 85 million rows, getting the count from ALL_TABLES takes 3 milliseconds. Getting the count from count(*) takes 3 seconds and similar time for count(primary_key). – Nathan Jul 20 '20 at 17:34
1

For a table with 52 rows, ALL_TABLES, count(*) and count(primary_key) take about the same amount of time. – Nathan Jul 20 '20 at 17:34

score 68 · Answer 2 · answered Dec 04 '09 at 05:10

68

If you want just a rough estimate, you can extrapolate from a sample:

SELECT COUNT(*) * 100 FROM sometable SAMPLE (1);

For greater speed (but lower accuracy) you can reduce the sample size:

SELECT COUNT(*) * 1000 FROM sometable SAMPLE (0.1);

For even greater speed (but even worse accuracy) you can use block-wise sampling:

SELECT COUNT(*) * 100 FROM sometable SAMPLE BLOCK (1);

answered Dec 04 '09 at 05:10

Jeffrey Kemp

59,135
14
106
158

is it possible to do this on a subquery? – uesports135 May 02 '16 at 17:05
5

No, the SAMPLE clause only works with a base table, unfortunately. – Jeffrey Kemp May 03 '16 at 12:14
can you think of any other ways to implement a similar concept on a subquery? – uesports135 May 03 '16 at 15:09
2

Apart from actually running the query and counting how many records come back, the only way I can think of is to use Oracle's optimiser stats - i.e. look at the cardinality on the top-level of the explain plan - but I'd only expect them to be somewhat representative to an order of magnitude and I can't see that being a reasonable alternative for any use case. – Jeffrey Kemp May 05 '16 at 01:19

score 32 · Accepted Answer · edited Jul 12 '13 at 15:13

32

Think about it: the database really has to go to every row to do that. In a multi-user environment my COUNT(*) could be different from your COUNT(*). It would be impractical to have a different counter for each and every session so you have literally to count the rows. Most of the time anyway you would have a WHERE clause or a JOIN in your query so your hypothetical counter would be of litte practical value.

There are ways to speed up things however: if you have an INDEX on a NOT NULL column Oracle will count the rows of the index instead of the table. In a proper relational model all tables have a primary key so the COUNT(*) will use the index of the primary key.

Bitmap index have entries for NULL rows so a COUNT(*) will use a bitmap index if there is one available.

edited Jul 12 '13 at 15:13

jdthood

5
2

answered Dec 03 '09 at 15:26

Vincent Malgrat

66,725
9
119
171

2

I have a primary key on this table on 4 columns, each of which is NOT NULL, and it still took 41 seconds to do a COUNT(*). Does the index have to be on a single column to make a COUNT(*) query fast? – Eli Courtwright Dec 03 '09 at 15:50
3

@Eli: The smaller the index key, the faster the query. If your table is big however, the `COUNT(*)` WILL take time no matter what. See some suggestions of alternatives to `COUNT(*)` in other answers (record the result in a materialized view, make a COUNT on a sample, ...) – Vincent Malgrat Dec 03 '09 at 16:13
For the record, I have seen a simple SELECT(*) take as much as 40mins. on a table with a primary key on 4 non-null numeric columns and 844 mio. entries. – JRA_TLL Feb 13 '17 at 16:54
@JRA_TLL: That's a kinda big table, if you want a precise count it will take some time. If you want an estimate Jeffrey's answer will be a lot faster (you can sample 0.01% of the blocks and still have a statistically representative number). – Vincent Malgrat Feb 14 '17 at 09:42

score 15 · Answer 4 · answered Dec 03 '09 at 15:35

If the table has an index on a NOT NULL column the COUNT(*) will use that. Otherwise it is executes a full table scan. Note that the index doesn't have to be UNIQUE it just has to be NOT NULL.

Here is a table...

SQL> desc big23
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 PK_COL                                    NOT NULL NUMBER
 COL_1                                              VARCHAR2(30)
 COL_2                                              VARCHAR2(30)
 COL_3                                              NUMBER
 COL_4                                              DATE
 COL_5                                              NUMBER
 NAME                                               VARCHAR2(10)

SQL>

First we'll do a count with no indexes ....

SQL> explain plan for
  2      select count(*) from big23
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /
select * from table)dbms_xplan.display)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 983596667

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  1618   (1)| 00:00:20 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG23 |   472K|  1618   (1)| 00:00:20 |
--------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
   - dynamic sampling used for this statement

13 rows selected.

SQL>

No we create an index on a column which can contain NULL entries ...

SQL> create index i23 on big23(col_5)
  2  /

Index created.

SQL> delete from plan_table
  2  /

3 rows deleted.

SQL> explain plan for
  2      select count(*) from big23
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 983596667

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  1618   (1)| 00:00:20 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG23 |   472K|  1618   (1)| 00:00:20 |
--------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
   - dynamic sampling used for this statement

13 rows selected.

SQL>

Finally let's build the index on the NOT NULL column ....

SQL> drop index i23
  2  /

Index dropped.

SQL> create index i23 on big23(pk_col)
  2  /

Index created.

SQL> delete from plan_table
  2  /

3 rows deleted.

SQL> explain plan for
  2      select count(*) from big23
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1352920814

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   326   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I23  |   472K|   326   (1)| 00:00:04 |
----------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
   - dynamic sampling used for this statement

13 rows selected.

SQL>

score 8 · Answer 5 · answered Dec 03 '09 at 15:36

Option 1: Have an index on a non-null column present that can be used for the scan. Or create a function-based index as:

create index idx on t(0);

this can then be scanned to give the count.

Option 2: If you have monitoring turned on then check the monitoring view USER_TAB_MODIFICATIONS and add/subtract the relevant values to the table statistics.

Option 3: For a quick estimate on large tables invoke the SAMPLE clause ... for example ...

SELECT 1000*COUNT(*) FROM sometable SAMPLE(0.1);

Option 4: Use a materialized view to maintain the count(*). Powerful medicine though.

um ...

score 6 · Answer 6 · answered Dec 03 '09 at 21:10

You can create a fast refresh materialized view to store the count.

Example:

create table sometable (
id number(10) not null primary key
, name varchar2(100) not null);

create materialized view log on sometable with rowid including new values;

create materialized view sometable_count
refresh on commit
as
select count(*) count
from   sometable;

insert into sometable values (1,'Raymond');
insert into sometable values (2,'Hans');

commit;

select count from sometable_count;

It will slow mutations on table sometable a bit but the counting will become a lot faster.

score 3 · Answer 7 · answered Dec 04 '09 at 13:51

The fastest way to get a count of a table is exactly what you did. There are no tricks you can do that Oracle doesn't already know about.

There are somethings you have not told us. Namely why do you think think this should be faster?

For example:

Have you at least done an explain plan to see what Oracle is doing?
How many rows are there in this table?
What version of Oracle are you using? 8,9,10,11 ... 7?
Have you ever run database statistics on this table?
Is this a frequently updated table or batch loaded or just static data?
Is this the only slow COUNT(*) you have?
How long does SELECT COUNT(*) FROM Dual take?

I'll admit I wouldn't be happy with 41 seconds but really WHY do you think it should be faster? If you tell us the table has 18 billion rows and is running on the laptop you bought from a garage sale in 2001, 41 seconds is probably not that far outside "good as it will get" unless you get better hardware. However if you say you are on Oracle 9 and you ran statistics last summer well you'll probably get a different suggestions.

The other answers point out that there are other faster ways to get the number of rows. Including the suspected counter internal to oracle. Is just not as accurate as count(*). — Arturo Hernandez, Nov 06 '15 at 19:45

score 0 · Answer 8 · answered Feb 04 '20 at 15:12

0

This worked well for me

select owner, table_name, nvl(num_rows,-1) 
from all_tables 
--where table_name in ('cats', 'dogs')
order by nvl(num_rows,-1) desc

from https://livesql.oracle.com/apex/livesql/file/content_EPJLBHYMPOPAGL9PQAV7XH14Q.html

answered Feb 04 '20 at 15:12

Harry L

95
1
7

score -1 · Answer 9 · edited Mar 12 '18 at 14:32

-1

You can have better performance by using the following method:

SELECT COUNT(1) FROM (SELECT /*+FIRST_ROWS*/ column_name 
FROM table_name 
WHERE column_name = 'xxxxx' AND ROWNUM = 1);

edited Mar 12 '18 at 14:32

Peter

913
1
12
24

answered Mar 12 '18 at 12:16

Akil Romauld Yesudhas

1

Looks like this query would never count beyond one. – Vadzim May 30 '19 at 10:54

score -4 · Answer 10 · answered Jan 27 '14 at 15:54

-4

You could use COUNT(1) instead

answered Jan 27 '14 at 15:54

11g

9

Note the comments associated with this [deleted answer](http://stackoverflow.com/a/10335036/) (10K rep required to see it). The comments say `COUNT(1)` and `COUNT(*)` are treated the same — which is normally true in any SQL database. It's an 'urban myth' that there's a difference (or if there's a DBMS where there is a difference, said DBMS has a severe oversight in its optimizer; there should be no difference). – Jonathan Leffler Sep 06 '16 at 06:37

Faster alternative in Oracle to SELECT COUNT(*) FROM sometable

10 Answers10

Linked