Skip to Main Content
  • Questions

  • Select count(*) on a billion records table.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ram.

Asked: April 07, 2016 - 7:54 pm UTC

Last updated: November 29, 2018 - 12:49 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hello Tom,

Thanks for all the great help.

I am writing a simple query which is Select count(*) from wsh_exceptions. Currently this table contains 1,091,130,564 records.
So because of this more number of records in this table, select count(*) is taking so much time. Alternately I am using num_rows column from dba_tables to know the number of rows.

Do we have any better way to user select count(*) on these big tables?

Thanks,
Ram.

and Connor said...

IF you have sufficient server power, you can do

select /*+ parallel */ count(*) from wsh_exceptions

if you are just after an approximation, you can do take a sample:

select 10 * count(*) from wsh_exceptions sample block (10);

or

select 5 * count(*) from wsh_exceptions sample block (20);

(ie, the two pairs of numbers multiply to 100)

Also, if there is

a) a column that contains no nulls, but is not defined as NOT NULL, and
b) there is an index on that column

you could try:

select /*+ index_ffs(t) */ count(*) from wsh_exceptions t where indexed_col is not null

Hope this helps.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you so much

rambabu konakanchi, April 08, 2016 - 3:46 am UTC

Thank you so much for the response and the help.

Query Rewrites

Rajeshwaran Jeyabal, April 08, 2016 - 5:39 am UTC

Depends on
1) How frequently the data changes on this table wsh_exceptions.
2) What fraction of data changes on this table.
3) The application nature surrounding this table.

We can think of having materialized view in place for query rewrites to make the count(*) to be faster.


rajesh@ORA12C> set autotrace on
rajesh@ORA12C> select count(*) from big_table ;

  COUNT(*)
----------
  10000000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 599409829

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 | 41119   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE |    10M| 41119   (1)| 00:00:02 |
------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         72  recursive calls
          0  db block gets
     157026  consistent gets
     156920  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA12C> set autotrace off
rajesh@ORA12C>
rajesh@ORA12C> create materialized view big_table_mv
  2  build immediate
  3  refresh on commit
  4  enable query rewrite as
  5  select mod(object_id,3)+1 x, count(*)
  6  from big_table
  7  group by mod(object_id,3)+1 ;

Materialized view created.

rajesh@ORA12C> set autotrace on
rajesh@ORA12C> select count(*) from big_table;

  COUNT(*)
----------
  10000000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2301198475

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |              |     1 |     6 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| BIG_TABLE_MV |     3 |    18 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA12C> set autotrace off
rajesh@ORA12C>


But again not sure about logic behind having count(*) - are we going to do something like this?

begin 
  select count(*) into l_cnt from some_table;   
  if l_cnt > 0 then 
    process_data ;
  end if;
end;
/


if that is the case, then count(*) would be the waste of resources, just process them rather than counting up.
Connor McDonald
April 08, 2016 - 6:50 am UTC

My first concern is:

For any table with the word "exceptions" in the name, how did it come to pass that there were 1billion to exceptions...to ...well...anything ?

That's an alarm bell right there.

Just one concern

AK, July 29, 2016 - 6:33 pm UTC

I had a very bad experience using just parallel hint without the degree specified in the query hint. Unfortunately, DBA did not apply a limit on parallel degree at table level, since my query didnt had degree specified in the hint, it took a lot of performance hit on the whole DB and every one was screaming. After that experience, I wont suggest anyone to use parallel hint without specifying degree
Connor McDonald
August 02, 2016 - 4:45 pm UTC

Or you might want to look at Auto DOP (if you're on 11.2 and up):

https://blogs.oracle.com/datawarehousing/entry/what_is_auto_dop
https://blogs.oracle.com/datawarehousing/entry/auto_dop_and_parallel_statemen

Chris

Alternate to count(*)

Paul Baker, August 19, 2016 - 4:55 pm UTC

Thank for this helpful info.. If you do not need up to the minute counts an alternate method would be to use data from the all_tables view:

select TABLE_NAME, num_rows, last_analyzed from all_tables where owner = '{your owner}' order by NUM_ROWS desc;

Another case

Pavel, November 28, 2018 - 12:54 pm UTC

Hello. We have another case.
We use ADF framework and it generates queryes like 'select count(1) from big_table'.
And we can't rewrite this query.
What can we do?
Connor McDonald
November 29, 2018 - 12:49 am UTC

Check out this blog post

http://andrejusb.blogspot.com/2012/05/how-to-disable-select-count-execution.html

That might solve things

David DUBOIS, November 29, 2018 - 11:07 am UTC

Hello Ram, You said "Do we have any better way to user select count(*) on these big tables?"

If you are using Oracle 12cr1 or later version, you can use the new function APPROX_COUNT_DISTINCT (col1) to go faster than count( distinct col1).

David

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions