Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ram.

Asked: August 12, 2001 - 11:00 pm UTC

Last updated: July 28, 2011 - 7:35 pm UTC

Version: 8

Viewed 50K+ times! This question is

You Asked

Hi,
Will the following code ever result in l_num_rec_count being more than 1 in any case?

SELECT count(1)
INTO l_num_rec_count
FROM <Table Name>
WHERE <condition1> AND <condition2>;


I am unable to find syntax of count(1) and I have to maintain a code with this syntax. Thanks in advance for your help.

Cheers,
Ram

and Tom said...

sure, that counts the number of NON-NULL 1's returned by that query. That is the same as "count(*)".

It is the same as "select count(<any non-null column>) ...."

count(1) is count(*) in disguise.

followup to comment one

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1156159920245

and take me up on my challenge then!  <b>Prove it to me, give that irrefutable test case that shows once and for all that count(1) is faster, better, cheaper then count(*)!</b>


I can only offer up cases that show they are in fact -- identical (over and over and over I can offer up these cases).  They do the same exact thing.

It'll only take one case to prove me wrong.  Here is one (in addition to the one in the link above) that shows count(*) and count(1) are IDENTICAL, the SAME, no different:

select count(1) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     11.97      18.33      22165      22166         72           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     11.98      18.34      22165      22166         72           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 334  (OPS$TKYTE)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
10000000   INDEX FAST FULL SCAN (object id 71204)

********************************************************************************

select count(*) from  t


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     11.64      16.82      22165      22166         72           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     11.65      16.82      22165      22166         72           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
10000000   INDEX FAST FULL SCAN (object id 71204)



<b>followup to comment three</b>

Badhri   -- take my challenge above!

Prove me wrong ! 

It only takes ONE CASE and I'll be wrong (but you'll never find it).  

If you look at the example I had already provided above, you'll see that the count(*) and count(1) <b>both used the index</b>.......

See also:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1156159920245

This is a silly discussion -- the proof is right above! they are the same!

Nuff said.

<b>followup to anonymous in the UK (comment 4 and maybe 5?? )</b>

Prove it, give me that test case.  You are wrong.  COUNT(*) and COUNT(1) are the *same* -- the same -- the same -- no different, the same.  Give us the SQLPlus script we can all run.

All you have to do is concoct 1 example -- just one, that shows otherwise.

"potentially" -- not valid here.

count(*) counts records in a table (it does NOT have to get the full record, it just needs to know there is a record and increments a count)

count(1) counts non null occurences of the constant 1 in a table.  It does not have to get the full record, it just needs to know there is a record and increments a count.

they are the same, identical, not different.


Please -- if you want to followup to this and say "count(1) is faster" -- put a test case -- or DONT POST a followup.

Try "select count(*) from all_objects" and "select count(1) from all_objects".

All Objects (being a fairly complex view)....


see
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1156159920245 <code>
for the proof that a view, a table -- i don't really care -- they are in fact the SAME.


If you did comment 5 below as well and that is your "proof" that count(1) is more efficient (not really sure if that is you or not -- not much of a proof as no one can see what ABC is, does or whatever...)

Lets look at the details:

The proof is in the pudding
===========================
select count(*) from ABC

COUNT(*)
----------
22766017

Elapsed: 00:00:06.25

Statistics
----------------------------------------------------------
19 recursive calls
401 db block gets
48481 consistent gets
0 physical reads
776 redo size
478 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory) 0 sorts (disk)
1 rows processed

select count(1) from ABC

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



All that shows is yet once again -- they are in fact the SAME. In the first count(*) -- you did a hard parse (see the recursive SQL) that accounts for the minimal, trivial difference in block gets and consistent gets.

Since you don't show a run time for the count(1), its rather meaningless.

Use TKPROF in the future as well -- set timing on in sqlplus is radically affected by autotrace (it counts autotrace as well). SQL_trace+tkprof is the only correct way to test this.

Rating

  (67 ratings)

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

Comments

Count(1)

Fuad Arshad, August 13, 2001 - 6:33 pm UTC

I know COunt(1) is count(*) in disguise but isnt count(1) faster. we've been evaluating all our queries and we've always found that count(1) had a better access time than count(*)

Thanks

Ram, August 14, 2001 - 5:06 am UTC

I searched in vain on the net for some info on this. This answer clarified the issue. Thanks..

count(1)

Badhri, August 14, 2001 - 7:02 am UTC

Hey Arshad,
U r right count(1) is faster than count(*)... I have heard people saying thet count(1) will run through Ur tables' index whereas count(*) doesn't...

Please let me know if I am right or wrong...


Count(1) is definitely quicker than count(*) on large complex views

Anonymous, August 15, 2001 - 9:38 am UTC

select count(1) from a view could potentially be quicker than count(*) provided that the view contains columns from more than 1 table etc. because of the use of temp tsp when doing count(*). So, yes count(1) is a better habbit to form than count(*) and potentially quicker. Its wrong to say that count(*) is always the same as count(1)



here's the proof

Anonymous, August 15, 2001 - 9:48 am UTC

The proof is in the pudding
===========================

select count(*) from ABC

COUNT(*)
----------
22766017

Elapsed: 00:00:06.25

Statistics
----------------------------------------------------------
19 recursive calls
401 db block gets
48481 consistent gets
0 physical reads
776 redo size
478 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

select count(1) from ABC

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


I believe you!

Tom, August 15, 2001 - 12:52 pm UTC

Tom --

Just wanted you to know that there are those of us who believe you. Thanks for the clarification.

Great lucid explanation

Joel, August 15, 2001 - 2:39 pm UTC

Great answer Tom!

OK - here's the proof. Negligible difference but the point is its not the same

A reader, August 16, 2001 - 4:33 am UTC

Less Parse / Exec and CPU time. The point I am trying to make here is its not the same.

>> select count(*) from ABC

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT


Event Elapsed in Statement(%) in Overall(%)
------ ---------- ---------------- --------------
CPU 0.07 100% 37%
Wait 0.00 0% 0%
------ ---------- ---------------- --------------
total 0.07 100% 37%



STATISTICS TOTALS FOR STATEMENT


call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
Parse 2 0.03 0.08 0 53 8 1 0
Exec 2 0.03 0.34 0 0 6 0 0
Fetch 4 0.01 10.26 0 0 0 0 2
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
total 8 0.07 10.68 0 53 14 1 2



EXECUTION PLANs FOR STATEMENT


Parse number : 1
Parsing User ID : 25
Relative starting time : Seconds 41.39
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208


Parse number : 2
Parsing User ID : 25
Relative starting time : Seconds 80.61
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208

=======================================

>> select count(1) from ABC

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT


Event Elapsed in Statement(%) in Overall(%)
------ ---------- ---------------- --------------
CPU 0.02 100% 11%
Wait 0.00 0% 0%
------ ---------- ---------------- --------------
total 0.02 100% 11%



STATISTICS TOTALS FOR STATEMENT


call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
Parse 2 0.00 0.01 0 2 8 1 0
Exec 2 0.01 0.00 0 0 6 0 0
Fetch 4 0.01 10.62 0 0 0 0 2
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
total 8 0.02 10.63 0 2 14 1 2

EXECUTION PLANs FOR STATEMENT

Parse number : 1
Parsing User ID : 25
Relative starting time : Seconds 64.14
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208


Parse number : 2
Parsing User ID : 25
Relative starting time : Seconds 110.57
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208




Select Count(1): How it works

Sam, August 21, 2001 - 12:56 pm UTC

I was reading a bug and found this:
problem is in count(col) to count(*) conversion
*** 03/23/00 05:46 pm ***
one workaround is to set event 10122 to turn off count(col) ->count(*)
optimization.
Another work around is to change the count(col) to count(*),
it means the same, when the col has a NOT NULL constraint.
The bug number is 1215372.

Tom Kyte
August 22, 2001 - 9:49 am UTC

Thanks for finding that. Just goes to show that count(*) and count(col) are the same (and that in fact count(col) is "optimized" into count(*) internally!!!!

I can explain the differences the user in the prior comments saw simply by "random-ness". If they looked at my examples, something count(*) is marginally (as they said "neglible") faster then count(1) and sometimes count(1) is marginally faster then count(*). This is due to the granularity of the clock used by the system -- we measure discrete events -- not a continous sequence of events, hence timing errors are avoidable (when the event you are measuring takes less then 1/100's of a second, we will get some rounding errors.

Geeze - be scientists for a moment

Jim Kennedy, August 22, 2001 - 10:27 am UTC

To expand on Tom's explanation of why sometimes count(1) is faster than count(*). To truly prove it you need an example and you need to run that example repeatedly. The limits of precision on your experiment overwhelm what you are measuring. You would need to run it many many times in order for the rounding error or randomness to settle down. For example, to test whether a coin is fair or not (fair being 50% of the time it lands heads and 50% of the time it lands tails) you have to flip (test) the coin a lot. (about 1,500 times to be sure with in 3% that it is fair; you can never be 100 % sure)

Quote from a book

Carol, August 22, 2001 - 11:06 am UTC

The only reason I believed COUNT(*) is faster than COUNT(1) is because I read it in 'Oracle Performance Tuning', second edition, O'Reilly & Associates, Inc, page 175. It says:

"Contrary to popular belief COUNT(*) is faster than COUNT(1). If the rows are returned via an index, counting the indexed column - for example, COUNT(EMP_NO) is faster still. The optimizer realizes from the existence of the index that the column must also exist (non-null). We tested the following statements on several different computers and found that COUNT(*) consistently runs between 15% and 20% faster than COUNT(1) and that COUNT(INDEXED_COLUMN) is 5% faster again."

Doesn't seem so true to me anymore.


AB, August 23, 2001 - 3:58 am UTC

Tom provides the answer as usual, backed up in spectacular form with concrete examples. How people can question it with "hearsay" evidence is beyond me. These are the same people that still believe explicit cursors are faster because of the single fetch - and how many times have we seen Tom prove us wrong on that ! The point is this - whatever you believed to be true in Oracle 7 with respect to the finer points of SQL tuning has somewhat changed in 8i. The database evolves and access methods improve, and if you don't keep up with it, your standard practices quickly become outdated and in cases, obsolete.

count(*) during tuning?

John, July 16, 2003 - 7:38 pm UTC

Hi Tom,

It is not about count(1), count(*)...
1. Is
select count(1) from (select....)
always quicker than just the
(select ....)
?

2. During performance tuning, is it a good idea to compare
the 2
select count(1) from (select....)
or would it skew the data and would not give you the correct comparison between the 2
(select ...)?

Thanks!

John





Tom Kyte
July 16, 2003 - 7:45 pm UTC

1) almost always -- that and it can and will use totally different PLANS.

never never select count(*) from ( query ) if you want to benchmark query. it is useless.

2) it would be utterly and 100% meaningless.

Thanks!

John, July 16, 2003 - 11:49 pm UTC

Thanks Tom! This question has bothered me for quite a while. I could not seem to pin down the answer. Good to know that I can always come here for consultation!

John

count(1)

Alvin, July 17, 2003 - 12:49 am UTC

I'll only believe in count(1) if it will give me atleast 10-15% less I/O. If not, then what's the point ? it might just confused other developer's/dba's not familiar with it.

Readability is important if you have tons of packages/scripts to maintain.

Tom Kyte
July 17, 2003 - 10:26 am UTC

huh?

count(1) is pointless.
count(*) is the right way

count(1) is internally optimized to count(*)

but -- i still don't get the followup here? count(anything) shouldn't be used for benchmarking unless the app itself selected count.

why count(1) is aesthetically better

mikito harakiri, July 17, 2003 - 8:33 pm UTC

For starters, optimizer is free to tranform any query into a semantically equivalent one. The issue is not about performance, then: we are arguing about what form is more "intuitive" only.

Suppose we have

table A
x y
- -
1 1
1 2
1 2

then how is that count card[(1,1),(1,2),(1,2)]=3, or card[1,1,2]=3 more "intuitively evident" than card[1,1,1]=3 ?
For math challenged, the sql translation of the above formulas is

select count(*) from A
select count(y) from A
select count(1) from A

correspondingly. I like the last form because I could similarly write

select sum(1) from A

which is much less ambiguous.

Tom Kyte
July 17, 2003 - 9:13 pm UTC

count(y) is not the same as count(*) or count(1), it in general returns a different answser.



with nulls one can never guess query result right

mikito harakiri, July 17, 2003 - 10:17 pm UTC

I thought that '*' could be always substituted with list of columns in any expression. But you are right, it can't:

select count(*) from (
select null a from dual
)

Now, that's intuitive!


Tom Kyte
September 24, 2004 - 12:13 pm UTC

why not? It is to me -- that is why I'm totally into count(*) and find count(1) as "something I see from someone who hasn't gotten into it yet"


count(*) is explicitly to count the cardinality of a "set".


count( expression ) is explicitly used to count the "number of non-null occurrences of expression"

Let's look at this for a minute. Take the word

"intuitive"

say you didn't know english, never read "the docs", decided to not understand the grammar, letters, pronunciation, nothing.

Intuitive would be "not intuitive" would it. it would just be gibberish.


Sorry that documentation is still relevant in 2004, we are working on sentient Oracle -- coming soon. You want have to use sql, just think at it :)




Tom is always right

Riyaz, September 23, 2004 - 7:07 am UTC

Tom is always right.

create table temp1(slno number, name varchar2(10), code varchar2(3));

insert into temp1(slno, name, code) values (1, '','1');
insert into temp1(slno, name, code) values (2, 'XXXX','1');
insert into temp1(slno, name, code) values (3, 'RRRR','1');
insert into temp1(slno, name, code) values (2, '','9');

scott@ORCL> select count(1) from temp1;

COUNT(1)
----------
4

scott@ORCL> select count(*) from temp1;

COUNT(*)
----------
4

scott@ORCL> select count(name) from temp1;

COUNT(NAME)
-----------
2


Tom Kyte
September 24, 2004 - 8:20 am UTC

no, no i'm not -- i'm always open to discussion.


In an interview, I might just push it to see how you react :)

David Aldridge, September 24, 2004 - 11:34 am UTC

>> Now, that's intuitive!

It doesn't have to be intuitive if it's documented :)

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions26a.htm#SQLRF00624 <code>

"If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr."

Finally - conclusive proof

Glenn A. Santa Cruz, October 11, 2004 - 2:16 pm UTC

I've found it! Absolute proof that count(*) is faster than count(1).

sql->alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.10
sql->select count(1) from dual;

COUNT(1)
----------
1

1 row selected.

Elapsed: 00:00:00.40
sql->select count(*) from dual;

COUNT(*)
----------
1

1 row selected.

Elapsed: 00:00:00.40

-- TKPROF:
select count(1)
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=87 us)
1 TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=71 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.11 3.11
********************************************************************************

select count(*)
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=94 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=79 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 2.30 2.30


--------------------------------------------------------------------------------
count(1) shows "SQL*Net message from client" = 3.11
count(*) shows "SQL*Net message from client" = 2.30

Research into the time problem led first to a comparison of the statements.
Careful examination revealed a subtle difference: a one byte discrepancy at
offset 14 of the SQL statement ( "*" vs. "1" ). Realizing this difference,
I was able to form two separate cases and document execution steps for each.

case 1: count(*)
----------------
1) left pinky holds down left shift key
2) right middle finger hits "("
3) right middle finger hits "*"
4) right ring finger hits ")"
Note that steps 1 and 2 are very nearly simultaneous, as are steps 3 and 4,
due to parallelism between the two hands. The majority of the time spent
in this case is the movement of right middle finger between steps 2 and 3

case 2: count(1)
----------------
1) left pinky holds down left shift key
2) right middle finger hits "("
3) left pinky releases left shift
4) left ring finger hits "1"
5) left pinky holds down left shift key
6) right ring finger hits ")"
Note that steps 1 and 2 are very nearly simultaneous, as are steps 5 and 6.
The majority of the time spent in this case is steps 3, 4, and 5.

Analysis: case 1 has 33% FEWER steps than case 2. Even with pipelining involved
(using both hands at once), case 2 must still perform more work to accomplish
what Oracle will ultimately consider the same thing. This may be defeated,
however, by clever use of the copy/paste subsystem of the operating system.
Using high-speed overhead keyboard cameras may reveal further important
information.

To ensure this was a reproducible case, I performed it on multiple machines
with similar findings. I furthermore discovered that both cases suffered
reduced performance after drinking a few beers.

The point is, my argument is equally as valid as some of the arguments I've
seen posted in this thread: utterly ridiculous and inconclusive. Unless one of
you is an Oracle engineer responsible for the code that actually interprets and
optimizes count(*), then you really can't argue that count(1) is faster than
count(*). You must provide irrefutable conclusive evidence to prove your case
that one is faster than the other, and that case must resist the test of this
peer community.



Tom Kyte
October 11, 2004 - 4:48 pm UTC



ROTFL :)

thanks (at first, i was reading this seriously.... )

Alberto Dell'Era, October 11, 2004 - 5:41 pm UTC

At least with cursor_sharing=force,
count(*) is faster then count(1) ...

dellera@ORACLE9I> declare
2 l_stmt varchar2(32767) default 'select ';
3 l_dummy number;
4 begin
5 for i in 1..3000 loop
6 l_stmt := l_stmt || 'count(*)+';
7 end loop;
8 l_stmt := l_stmt || '0 from dual';
9 execute immediate l_stmt into l_dummy;
10 end;
11 /

Same as before, with count(1) instead of count(*):

1 declare
2 l_stmt varchar2(32767) default 'select ';
3 l_dummy number;
4 begin
5 for i in 1..3000 loop
6 l_stmt := l_stmt || 'count(1)+';
7 end loop;
8 l_stmt := l_stmt || '0 from dual';
9 execute immediate l_stmt into l_dummy;
10* end;

********************************************************************************
select count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)

(snip)

+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)
+:"SYS_B_0"
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.10 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.05 0.10 0 3 0 1

********************************************************************************
select count(:"SYS_B_0000")+count(:"SYS_B_0001")+count(:"SYS_B_0002")

(snip)

+count(:"SYS_B_2997")+count(:"SYS_B_2998")+count(:"SYS_B_2999")
+:"SYS_B_3000"
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.08 2.16 0 0 0 0
Execute 1 0.29 0.57 0 0 0 0
Fetch 1 0.01 0.02 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.38 2.75 0 3 0 1

The increase in parse cpu has to be obviously expected,
the increase in execute cpu too (more binds),
the increase in fetch cpu is probably due to the need to check for :SYS_* nullity.

Don't know which is the lesser evil between count(1) and cursor_sharing=force, anyway ;)



Count(1) versus Count(0)

Pritesh Jani, April 28, 2005 - 9:11 am UTC

Hi Tom,
I am one of your biggest fan. I have tested this on Oracle 10g where I pumped 154154 rows into test_emp table. And I got difference, count(1) is still faster than count(*).
I always think you are right. Please correct me if I am wrong / Explain why its behaving in such a way.

drop table t2;
create table t2 as select * from test_emp;
create or replace view v_test
as
select t2.*
from t1, t2
where t2.empno = t1.col_number;

alter system flush shared_pool;
select count(*) from v_test;
alter system flush shared_pool;
select count(1) from v_test;


‘ora10g’alter system flush shared_pool;

System altered.

‘ora10g’select count(*) from v_test;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=699 Card=1 Bytes=2
6)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=699 Card=98775 Bytes=2568150)
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=62 Card=9877
5 Bytes=1284075)

4 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=215 Card=139
650 Bytes=1815450)





Statistics
----------------------------------------------------------
1603 recursive calls
0 db block gets
1795 consistent gets
1773 physical reads
0 redo size
379 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
35 sorts (memory)
0 sorts (disk)
1 rows processed

‘ora10g’alter system flush shared_pool;

System altered.

‘ora10g’select count(1) from v_test;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=699 Card=1 Bytes=2
6)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=699 Card=98775 Bytes=2568150)
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=62 Card=9877
5 Bytes=1284075)

4 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=215 Card=139
650 Bytes=1815450)





Statistics
----------------------------------------------------------
1068 recursive calls
0 db block gets
1688 consistent gets
306 physical reads
0 redo size
379 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed

Regards,

Pritesh.

Tom Kyte
April 28, 2005 - 9:37 am UTC

from this how did you come to the conclusion (wrong conclusion by the way)?

why would you flush the shared pool???????

you are timing and measuring the work oracle does to parse your statement in an empty shared pool -- you don't want to do that.

select count(*) from t;
select count(1) from t;


then run them and measure them. NO FLUSHING OF anything.

Which is faster : count(rowid) or count(*)

A reader, April 28, 2005 - 11:10 am UTC

Tom,
Which is faster
1)select count(*) from abc ;
2)select count(rowid) from abc ;

Tom Kyte
April 28, 2005 - 1:51 pm UTC

count(*) is the only (right) way to get a count of rows.

count(rowid) askes for the count of non-null rowids. why bother to check the non-nullness if all you want is a count.

count(*)count(*)count(*)count(*)count(*)count(*)count(*)count(*)count(*)count(*)

Of course, I could try it myself and find out...

A reader, April 28, 2005 - 11:26 am UTC


Which should be the Preferred way

A reader, April 28, 2005 - 1:42 pm UTC

Count(*) or Count(Rowid) to get the total num of rows from a table ,or which could be used in any sub select.

Just reframmed the question in view of the reader comment above.

Tom Kyte
April 28, 2005 - 2:06 pm UTC

count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), ...... count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), ........


it means "count the rows"

count(rowid) says "count non-null rowids", you don't want to count non-null rowids, you want to count rows

count(*), count(*), count(*), count(*), count(*), count(*), count(*), .............

what about

A reader, April 28, 2005 - 3:17 pm UTC

count(2.5)
count(2.71828)
count(3.1415)

Tom Kyte
April 28, 2005 - 4:19 pm UTC

count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), count(*), .....


:)



DieHard counters

Mikito Harakiri, April 28, 2005 - 4:28 pm UTC

The argument for count is treated differently from the argument for sum(), max(), avg(), etc. If this doesn't raise a red flag for you, then something is wrong with your critical thinking.

An "intutive" count() argument usages that I've seen in database world (which is BTW wider than oracle) are:

1. Predicate as an argument

count(sal>1000) which is a shorthand for

select count from (
select * from emp where sal > 1000
)

Note how nicely it encompases count(sal not null)

2. Group by clause

count(distinct sal) which is the same as count(group by sal)

which is abbreviation for

select count from (
select distinct sal from emp
)

and

select count from (
select sal from emp group by sal
)

respectively.


This kind of argument can be added as a second parameter to sum, max and the other aggregate functions, of course. Then,

select sum(sal, sal>500) from emp

becomes abbreviation for

select sum(sal) from (
select sal from emp where sal > 500
)

Again we are talking elementary consistency here.

count(9)

DaPi, April 28, 2005 - 4:36 pm UTC

</code> http://www.dbasupport.com/forums/showthread.php?s=&threadid=40518 <code>
reveals that:

- count(9) is fastest for one fingered typists

- count(1) gets conveted to count(*) anyway

I wonder how did he notice that count(1) is converted to count(*)

Mikito Harakiri, April 28, 2005 - 6:43 pm UTC

The projection column has been added to the plan_table in 10g only and the post in the exchange above is dated Dec 2003.

select count(1) from sys.obj$;
-------------^

OPERATION OBJECT NAME PROJECTION
--------- ----------- ----------
SORT (#keys=0) COUNT(*)[22]
------------------------------------------^
INDEX I_OBJ1


Tom Kyte
April 28, 2005 - 6:50 pm UTC

read this post:

<quote>
I was reading a bug and found this:
problem is in count(col) to count(*) conversion
*** 03/23/00 05:46 pm ***
one workaround is to set event 10122 to turn off count(col) ->count(*)
optimization.
Another work around is to change the count(col) to count(*),
it means the same, when the col has a NOT NULL constraint.
The bug number is 1215372.
</quote>


basically that is a bug logged against the fact that count(<constant>) is rewritten to count(*). Has been for years and years.

Reason: count(<constant>) was slow slow slow (you actually had to evaluate it and see "not null?") and this was a performance fix because everyone kept writing the count(1) stuff. counting rows that have 1's is slower than counting rows.


And the ability to get that information was publically released recently, but has been available if you knew how for a long time.


sign me: loving count(*), it is just right. aggregates work, all you have to do is read the documentation and understand how they were implemented.

Oh man, this topic is addicive...

Mikito Harakiri, April 28, 2005 - 6:56 pm UTC

Apparently

count(2+3) is converted to count(5), not to count(*)

and

count(-1)

is not converted at all...

Tom Kyte
April 28, 2005 - 7:01 pm UTC

count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*) count(*)

just use it.

From where This rumours

Bhavesh Patel, April 29, 2005 - 4:54 am UTC

hi Tom,
I also believed that count(1) is faster, and always used it..
But after reading this topic i came to know that there are no differences.
Where this rumours comes that count(1) is faster
than count(*)?



Tom Kyte
April 29, 2005 - 8:28 am UTC

Myths are made up over time and gain a life of their own.

That is why I never "just say stuff", I show it.

Do not trust anyone that says "Trust me, I just know". Odds are -- they know not of what they speak.

Many times, when I give an off the cuff answer -- it is

a) partially right, partially wrong
b) entirely right
c) totally wrong

(life is tri-valued logic :)


Those myths

DaPi, April 29, 2005 - 9:48 am UTC

"Where this rumours comes that count(1) is faster than count(*)?"

I've wondered if it is descendant the myth that says:
"where exists (select 1 from . . . . " OBVIOUSLY MUST be faster than
"where exists (select * from . . . . "
Well it MUST BE because you have to retrieve the WHOLE ROW in the second case. (Removes tongue from cheek.)

The real difference comes down to typing again! NULL is 3 chars longer and * moves all over the keyboard depending on the langauge :)

Tom Kyte
April 29, 2005 - 10:12 am UTC

I prefer where exists (select null from .... ) for semantic reasons, but yes the database "knows" it needs not retrieve the table data

count(1) vs count(*) vs count(9)

Abhishek, June 13, 2005 - 4:01 pm UTC

Answer: as tom puts it
COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)


count(2): The Next Generation

David Conrad, June 21, 2005 - 6:49 pm UTC

Wouldn't count(*)count(*)count(*)... take more time because it has to read the row several times? Just kidding!

A DBA, mad as a bat,
After donning his best tin foil hat,
Cleared out the shared pool
And his profiling tool
Showed count one outperforming count splat.

(count(*) pronounced "count splat" by analogy with "rm -rf *", pronounced "rum ruff splat".)

A reader, December 28, 2005 - 10:44 am UTC


Semantic Null

SeánMacGC, January 05, 2007 - 9:18 am UTC

Hello Tom,
Not wishing to be a pedant or such, but you state:

"I prefer where exists (select null from .... ) for semantic reasons..."

But... NULL can be defined as 'the absence of a value', and as such I don't believe "where exists (select the absence of a value from...)" cuts it semantically ;o)

I think we should always be dealing with values here, whether existentially or not.


Tom Kyte
January 05, 2007 - 10:46 am UTC

"where exists" is a set operation, it isn't looking at the columns.

Semantic Nulls

SeánMacGC, January 07, 2007 - 9:50 am UTC

Yes Tom, I
appreciate that exists is a set operation, but that's not particularly relevant here, and I still hold to the thrust of my point -- that it is not wholly sensible to be checking for the absence of values/sets, i.e., in addition to being counterintuitive, it is somewhat illogical semantically.
Tom Kyte
January 07, 2007 - 8:21 pm UTC

no, there is either

a) a non empty set
b) an empty set

the values of the attributes in the set - not meaningful.

we'll have to agree to disagree, I very must dislike:

where exists (select 1 from ...)


it is like

select count(1) from ....


to me, just distasteful

Semantic Nulls

SeánMacGC, January 08, 2007 - 3:29 am UTC

OK, we'll agree (to disagree), because I very much dislike:

'...where [not] exists (select null from...)'

:o)

A reason to always use COUNT(*)

Drew, March 19, 2008 - 3:29 pm UTC

As of 11g, there's a great reason to always use COUNT(*).

Try this:

asmith@DEV> create table t1 (c1 number);

Table created.

asmith@DEV> create view v1 as
  2  select count(1) count from t1;

View created.

asmith@DEV> create view v2 as 
  2  select count(*) count from t1;

View created.

asmith@DEV> select object_name, object_type, status
  2  from user_objects
  3  where (object_name in ('V1','V2'))
  4  and object_type = 'VIEW';

OBJECT_NAME
----------------------------
OBJECT_TYPE         STATUS
------------------- -------
V1
VIEW                VALID

V2
VIEW                VALID


asmith@DEV> alter table t1 add (c2 number);

Table altered.

asmith@DEV> select object_name, object_type, status
  2  from user_objects
  3  where (object_name in ('V1','V2'))
  4  and object_type = 'VIEW';

OBJECT_NAME
----------------------------
OBJECT_TYPE         STATUS
------------------- -------
V1
VIEW                INVALID

V2
VIEW                VALID

A view using count(*) on a table will remain valid when columns are added to the table.

See:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/dependencies.htm#insertedID4

Compare of COUNT(*), COUNT(1) and SUM(1)

Cindy K, July 06, 2008 - 8:50 pm UTC

I was working on a query, trying to understand what I was told about SUM was supposed to be faster than count.  (still not sure about that - for some strange reason, using DECODE and SUM was exponentially faster than a union and a condition in a WHERE clause.)...but back to the experiment.  

So I did a SELECT TABLE_NAME, NUM_ROWS from DBA_TABLES, and took the largest table  - approx. 15.5 Million rows.  
(I was pulling from my Mainframe days and using large volumes of data to measure speed.)

So I tried Selecting COUNT(*), COUNT(1) and SUM(1)...and here are my results:
 
20:21:12 USRT@PROD> select count(*) from ES_LOG;

  COUNT(*)
----------
  15544308

Elapsed: 00:00:12.01
20:22:36 USRT@PROD> select count(1) from ES_LOG;

  COUNT(1)
----------
  15544308

Elapsed: 00:00:01.07
20:22:51 USRT@PROD> select sum(1) from  ES_LOG;

    SUM(1)
----------
  15544308

Elapsed: 00:00:03.01
20:23:10 USRT@PROD>   

BUT when I went back and did the Select count(*) again and count(1), they came back with the same times.  

is this some kind of buffer issue? 

Tom Kyte
July 07, 2008 - 11:59 am UTC

absolutely a "buffer" issue.

sql plus timing - don't use it. so many things will affect it run to run.

Use tkprof and sql_trace - then you can see the work done by each.


count(*) is the ONLY CORRECT and PROPER WAY TO COUNT

count(1) - internally, we optimize that back to count(*) in order to not slow things down, since so many developers for whatever reason bought into the myth that count(1) is better than count(*)

sum(1) - that would just be wrong, semantically and otherwise.


count(*) says exactly what you are trying to do.


this:

... for some
strange reason, using DECODE and SUM was exponentially faster than a union and
a condition in a WHERE clause.) ...

is too vague to even comment on :)

Potential Source of the Myth?

Roderick, July 08, 2008 - 4:06 am UTC

I vaguely remember in the early Version 6 (or maybe V5) days, that
select count(*) from t@dblink;
would pull all the data from a remote database to the local one to be counted. So one optimization was to do a count(1) instead to cut down on the sqlnet traffic. I think Version 7 was when the count() function would also be passed over the dblink.
.
But then again even all that may have been a myth.
Tom Kyte
July 08, 2008 - 9:38 am UTC

interesting, could be - I didn't have a network with version 5 :) I don't have a v6 instance to test with anymore....

pc, August 11, 2008 - 5:24 am UTC

for select count(1) statement, how can we do it with SQLDA?

e.g. to select total row count for a table with table name entered by user during runtime.

Tom Kyte
August 12, 2008 - 8:25 am UTC

you would not need a sqlda for type-4 dynamic sql.

build a string - 'select count(*) from ' || table_name (do not use count(1), makes you look like you don't know sql :) )

and then execute it if you are using pro*c
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_13dyn.htm#i2342

What about memory consumption

Nelson, August 07, 2009 - 5:20 pm UTC

Tom,
Is there are difference between count(*) and count(1) in terms of the amount of memory used.
Does count(*) fetches the whole row into memory and count(1) does not?
Thanks
Tom Kyte
August 07, 2009 - 5:41 pm UTC

ctl-f for the word

bug


on this page to read about the optimization we put into the server to silently rewrite "select count(1)" as "select count(*)" since select count(1) would be more resource intensive than count(*) is


why do you think count(*) would have to read the entire row??????

count(*) says "tell me how many rows are in the table"

it doesn't need to read the entire row into memory. why do you think it would?

What about memory consumption II

Nelson, August 10, 2009 - 11:48 am UTC

I did not think that it needs to read the whole row into memory.
I heard it from a co-worker who says that count(1) is better than count(*).
I can see in the statistics the sorts in memory but have no idea how to prove that memory consumption does not play a part in this.
Thanks,

Tom Kyte
August 13, 2009 - 8:46 am UTC

Ask your co-worker to

a) read this page
b) provide any evidence - ANY EVIDENCE - to the contrary.

ctl-f for bug on this page to read about the *documented fact* that a bug was created an resolved a long time ago - and that bug was to "internally rewrite count(1) as count(*) in order to make the count(1) that people erroneously believe to be better perform at least as well as the correct way of doing it - count(*)"

Ask them for ANY EVIDENCE whatsoever. Evidence does not include "I heard it was so"

Count (*) gives the WRONG RESULT in a 'RETURNING CLAUSE'

Paul Hennebry, August 13, 2009 - 7:30 am UTC

Hi Tom

I delete a single row, using the 'RETURNING' clause to store the count of rows affected in the 'delcount' variable:

Count(*) tells me no rows were deleted
Count(1) tells me correctly that 1 row was deleted.

Environment : SQL*Plus: Release 10.2.0.2.0, Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

set serveroutput on
set verify 1

variable delcount number;

create table mytable (col1 varchar(1));
insert into mytable values ('a');
commit;

delete mytable returning count (*) into :delcount;
exec dbms_output.put_line ('COUNT (*) RETURNS ' ||:delcount);

rollback;

delete mytable returning count (1) into :delcount;
exec dbms_output.put_line ('COUNT (1) RETURNS ' ||:delcount);

rollback;



Is this an Oracle bug or can you explain what I'm missing?

Thanks!
Tom Kyte
August 13, 2009 - 12:29 pm UTC

...
I delete a single row, using the 'RETURNING' clause to store the count of rows affected in the 'delcount' variable:
....

sql%rowcount is what you wanted ( or whatever your API provides already, the all give you the ability to know the number of rows affected )

but yes, that looks like erroneous behavior, can you file a bug?

Thanks

Paul Hennebry, August 17, 2009 - 5:32 am UTC

Thanks Tom.

I'll raise a bug report. I'm a freelancer at a large bank.
My access to Metalink is coming, but its not going go be quick...

Paul

not that bug in 9i

Duke Ganote, August 17, 2009 - 2:28 pm UTC

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> set serveroutput on
SQL> set verify 1
SP2-0265: verify must be set ON or OFF
SQL>
SQL> variable delcount number;
SQL>
SQL> create table mytable (col1 varchar(1));

Table created.

SQL> insert into mytable values ('a');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> delete mytable returning count (*) into :delcount;
delete mytable returning count (*) into :delcount
                         *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> exec dbms_output.put_line ('COUNT (*) RETURNS ' ||:delcount);
COUNT (*) RETURNS

PL/SQL procedure successfully completed.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> delete mytable returning count (1) into :delcount;
delete mytable returning count (1) into :delcount
                         *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> exec dbms_output.put_line ('COUNT (1) RETURNS ' ||:delcount);
COUNT (1) RETURNS

PL/SQL procedure successfully completed.

SQL>
SQL> rollback;

Rollback complete.

is count(1) faster than count(*)? hardly

Duke Ganote, August 18, 2009 - 10:39 am UTC

I'ven't run the trace, but I'm thinking that with this bug, count(*) is still faster than count(1). Wrong, but faster. I feel better, don't you? :)

SR Number 7657427.994

Duke Ganote, August 18, 2009 - 12:58 pm UTC

SR Submitted Date 18-Aug-2009 09:31:40 GMT
Product Version 10.2.0.2.0
Severity 3
Brief Problem Statement Count(*) incorrect; Count(1) correct -- rows deleted

Using select count(*) in returning clause

Anand, August 19, 2009 - 3:09 am UTC

I cannot believe, that there's a bug using select count(*) or select count(1) in the returning clause.....

Count(1) and Count(*)

W, August 22, 2009 - 11:48 pm UTC

Actually, count(1) is faster than count(*) on an oracle db.
In mysql, it is the same and probably mssql too.
Tom Kyte
August 25, 2009 - 9:18 am UTC

Actually, I cannot believe you

a) read this page
b) wrote that


how "not smart"

did you even think to measure it?

Actually, as stated more than once - count(1) is REWRITTEN INTERNALLY TO BE COUNT(*) so as to make count(1) at least as fast as count(*). that is, so many developers made up this "count(1) is faster than count(*)" that we actually had to rewrite count(1) to count(*)


critical reading, read about it.




drop table t;

create table t as select * from all_objects;
exec dbms_stats.gather_table_stats( user, 'T' );

@trace
declare
    l_n number;
begin
for i in 1 .. 100
loop
    select count(*) into l_n from t;
    select count(1) into l_n from t;
end loop;
end;
/
disconnect
connect /
@trace
declare
    l_n number;
begin
for i in 1 .. 100
loop
    select count(*) into l_n from t;
    select count(1) into l_n from t;
end loop;
end;
/
@tk "sys=no"




trace.sql:
alter session set events '10046 trace name context forever, level 12';


tk.sql:
column trace new_val TRACE

select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/

disconnect
!tkprof &TRACE ./tk.prf &1
connect /
edit tk.prf




SELECT COUNT(*) FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100      0.72       0.74          0      69800          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.72       0.74          0      69800          0         100
********************************************************************************
SELECT COUNT(1) FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100      0.72       0.72          0      69800          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.72       0.73          0      69800          0         100

@W

Duke Ganote, August 23, 2009 - 6:48 pm UTC

In my opinion, making the claim -- without any demonstration or proof -- that "X is faster than Y" verges perilously on the unprofessional. Especially when previous demonstrations on this thread have shown the opposite. If the results were different when you tried it, show what you observed -- quantitatively.

As for the SR on RETURNING count(*) or count(1), Oracle support has created BUG 8827474.

bugged

Duke Ganote, August 24, 2009 - 2:49 pm UTC

Bug updated to say it's the same as Bug No. 5869609, Filed 07-FEB-2007

Reader, August 24, 2009 - 3:00 pm UTC

Tom,

How does the select count(*) work on a range partitioned daily?

Is the count taken from NUM_ROWS from user_tab_partitions?
Tom Kyte
August 25, 2009 - 9:47 am UTC

num_rows would only be used by the optimizer - it is never used to return a answer to a query like 'count(*)'


if you do a select count(*) from T
regardless of whether t is partitioned or not
and there exists an index on a not-nullable column, we will probably index fast full scan that index, else we will full scan the table.

NUM_ROWS just an estimate,not an actual

Duke Ganote, August 25, 2009 - 10:24 am UTC

As Tom said, NUM_ROWS just an estimate,not an actual. And caching affects your perceived performance (that's why there's a cache!)

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
 select sum(num_rows)
 from user_tab_partitions
 where table_name = 'POLICY_AND_QUOTE';

SUM(NUM_ROWS)
-------------
   39,826,281 -- just an estimate

SQL> set serveroutput off

Session altered.

SQL> set timing on
SQL> select /*+ gather_plan_statistics */ count(*) from policy_and_quote;

  COUNT(*)
----------
39,884,587  -- the actuality

Elapsed: 00:00:30.66 -- wow, 30 seconds for count(*) !!!!

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------
|Id|Operation                   |Name      |Starts |E-Rows|A-Rows|   A-Time   |Buffers|
---------------------------------------------------------------------------------------
| 1|SORT AGGREGATE              |          |     1 |    1 |    1 |00:00:29.77 |  1574 |
| 2|PARTITION RANGE ALL         |          |     1 |  39M |21520 |00:00:33.43 |  1574 |
| 3|BITMAP CONVERSION COUNT     |          |   301 |  39M |21520 |00:00:30.27 |  1574 |
| 4| BITMAP INDEX FAST FULL SCAN|PQ_SRC_BIT|   301 |      |21520 |00:00:29.73 |  1574 |

SQL> select /*+ gather_plan_statistics */ count(1) from policy_and_quote;

  COUNT(1)
----------
39,884,587

Elapsed: 00:00:00.15 -- so much faster: less than a second !!

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------
|Id|Operation                   |Name      |Starts |E-Rows|A-Rows|   A-Time   |Buffers|
---------------------------------------------------------------------------------------
| 1|SORT AGGREGATE              |          |     1 |    1 |    1 |00:00:00.09 |  1574 |
| 2|PARTITION RANGE ALL         |          |     1 |  39M |21520 |00:00:00.13 |  1574 |
| 3|BITMAP CONVERSION COUNT     |          |   301 |  39M |21520 |00:00:00.61 |  1574 |
| 4| BITMAP INDEX FAST FULL SCAN|PQ_SRC_BIT|   301 |      |21520 |00:00:00.08 |  1574 |

SQL> select /*+ gather_plan_statistics */ count(*) from policy_and_quote;

  COUNT(*)
----------
39,884,587

Elapsed: 00:00:00.20  -- but wait, now count(*) is faster too!

---------------------------------------------------------------------------------------
|Id|Operation                   |Name      |Starts |E-Rows|A-Rows|   A-Time   |Buffers|
---------------------------------------------------------------------------------------
| 1|SORT AGGREGATE              |          |     1 |    1 |    1 |00:00:00.08 |  1574 |
| 2|PARTITION RANGE ALL         |          |     1 |  39M |21520 |00:00:00.11 |  1574 |
| 3|BITMAP CONVERSION COUNT     |          |   301 |  39M |21520 |00:00:00.57 |  1574 |
| 4| BITMAP INDEX FAST FULL SCAN|PQ_SRC_BIT|   301 |      |21520 |00:00:00.07 |  1574 |


Amazing what the cache will do for you.

Bug No. 5869609

Sokrates, August 25, 2009 - 11:11 am UTC

wow.

"May be it is an expected behaviour."
...
"use a column or pseudo-column insted of * "

which proves that count(*) is NOT the same as count(<column>)

Tom Kyte
August 25, 2009 - 8:29 pm UTC

not to fear, I read the entire bug, the original poster of the bug said "may be it is an expected behavior" - and they shouldn't.

It is a bug, it is fixed in 11.2, backportable if need be.

also see similar question on count(*) vs count(1)

Duke Ganote, August 25, 2009 - 11:38 am UTC

Greg Norris, August 25, 2009 - 4:02 pm UTC

> which proves that count(*) is NOT the same as count(<column>)

I don't think anyone claimed the two versions were identical. I hope not... they answer fundamentally different questions.

count(*) --> counts complete rows
count(<column>) --> counts non-null occurrences of <column>

Bala, March 23, 2010 - 4:11 pm UTC

Hi Tom,

Please evaluate these queries and enlighten us.


SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 23 16:09:53 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT COUNT(*)  FROM dual WHERE 1 = 2;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*)  FROM dual WHERE 1 = 2 GROUP BY dummy;

no rows selected

SQL> 

does not return any rows. We were expecting 0 (Zero)

Is this an intended behavior?

Tom Kyte
March 24, 2010 - 3:49 am UTC

yes, the rule is:

an aggregate without any group by will always return AT LEAST one row and AT MOST one row.

an aggregate with a group by will return one row per each observed group by set of values (between 0 and as many rows as you can have).


an aggregate without any group by - always one row, even on an empty table.



Timing of COUNT (*) and COUNT (1)

Bill, March 25, 2010 - 1:01 pm UTC

I wrote a PL/SQL script to be run under SQL*Plus that times SELECT COUNT (*) and SELECT COUNT (1)
queries. The script has the following features:

1) It does a SELECT COUNT (*) and a SELECT COUNT (1) query against a DBA_OBJECTS view that
contains over 68,000 rows.

2) It flushes the BUFFER_CACHE and the SHARED_POOL before each query.

3) It perform 10 sets of the two queries, timing each one down to six decimal places.

4) It alternates the ordering of the two queries, with SELECT COUNT (*) submitted first in odd
sets and SELECT COUNT (1) submitted first in even sets.

Below are the results I received from two runs of the script:

Run #1:

COUNT (*) time: +00 00:00:08.100015
COUNT (1) time: +00 00:00:05.726067
COUNT (1) beat COUNT (*) by +00 00:00:02.373948
COUNT (1) is leading COUNT (*) by +00 00:00:02.373948
COUNT (1) time: +00 00:00:04.690972
COUNT (*) time: +00 00:00:07.283698
COUNT (1) beat COUNT (*) by +00 00:00:02.592726
COUNT (1) is leading COUNT (*) by +00 00:00:04.966674
COUNT (*) time: +00 00:00:09.697947
COUNT (1) time: +00 00:00:08.654737
COUNT (1) beat COUNT (*) by +00 00:00:01.043210
COUNT (1) is leading COUNT (*) by +00 00:00:06.009884
COUNT (1) time: +00 00:00:09.378757
COUNT (*) time: +00 00:00:07.982754
COUNT (*) beat COUNT (1) by +00 00:00:01.396003
COUNT (1) is leading COUNT (*) by +00 00:00:04.613881
COUNT (*) time: +00 00:00:07.393250
COUNT (1) time: +00 00:00:07.618329
COUNT (*) beat COUNT (1) by +00 00:00:00.225079
COUNT (1) is leading COUNT (*) by +00 00:00:04.388802
COUNT (1) time: +00 00:00:07.059480
COUNT (*) time: +00 00:00:05.122013
COUNT (*) beat COUNT (1) by +00 00:00:01.937467
COUNT (1) is leading COUNT (*) by +00 00:00:02.451335
COUNT (*) time: +00 00:00:06.239745
COUNT (1) time: +00 00:00:09.225688
COUNT (*) beat COUNT (1) by +00 00:00:02.985943
COUNT (*) is leading COUNT (1) by +00 00:00:00.534608
COUNT (1) time: +00 00:00:07.194247
COUNT (*) time: +00 00:00:06.923286
COUNT (*) beat COUNT (1) by +00 00:00:00.270961
COUNT (*) is leading COUNT (1) by +00 00:00:00.805569
COUNT (*) time: +00 00:00:06.084537
COUNT (1) time: +00 00:00:07.977888
COUNT (*) beat COUNT (1) by +00 00:00:01.893351
COUNT (*) is leading COUNT (1) by +00 00:00:02.698920
COUNT (1) time: +00 00:00:06.621696
COUNT (*) time: +00 00:00:06.628764
COUNT (1) beat COUNT (*) by +00 00:00:00.007068
COUNT (*) is leading COUNT (1) by +00 00:00:02.691852
Overall COUNT (*) beat COUNT (1) by +00 00:00:02.691852

Run #2:

COUNT (*) time: +00 00:00:08.203430
COUNT (1) time: +00 00:00:07.490769
COUNT (1) beat COUNT (*) by +00 00:00:00.712661
COUNT (1) is leading COUNT (*) by +00 00:00:00.712661
COUNT (1) time: +00 00:00:08.254991
COUNT (*) time: +00 00:00:08.045552
COUNT (*) beat COUNT (1) by +00 00:00:00.209439
COUNT (1) is leading COUNT (*) by +00 00:00:00.503222
COUNT (*) time: +00 00:00:07.211382
COUNT (1) time: +00 00:00:06.782487
COUNT (1) beat COUNT (*) by +00 00:00:00.428895
COUNT (1) is leading COUNT (*) by +00 00:00:00.932117
COUNT (1) time: +00 00:00:05.586959
COUNT (*) time: +00 00:00:07.043175
COUNT (1) beat COUNT (*) by +00 00:00:01.456216
COUNT (1) is leading COUNT (*) by +00 00:00:02.388333
COUNT (*) time: +00 00:00:08.388103
COUNT (1) time: +00 00:00:09.305080
COUNT (*) beat COUNT (1) by +00 00:00:00.916977
COUNT (1) is leading COUNT (*) by +00 00:00:01.471356
COUNT (1) time: +00 00:00:11.971294
COUNT (*) time: +00 00:00:09.546646
COUNT (*) beat COUNT (1) by +00 00:00:02.424648
COUNT (*) is leading COUNT (1) by +00 00:00:00.953292
COUNT (*) time: +00 00:00:07.936489
COUNT (1) time: +00 00:00:06.256901
COUNT (1) beat COUNT (*) by +00 00:00:01.679588
COUNT (1) is leading COUNT (*) by +00 00:00:00.726296
COUNT (1) time: +00 00:00:08.104579
COUNT (*) time: +00 00:00:05.415139
COUNT (*) beat COUNT (1) by +00 00:00:02.689440
COUNT (*) is leading COUNT (1) by +00 00:00:01.963144
COUNT (*) time: +00 00:00:07.566783
COUNT (1) time: +00 00:00:06.008076
COUNT (1) beat COUNT (*) by +00 00:00:01.558707
COUNT (*) is leading COUNT (1) by +00 00:00:00.404437
COUNT (1) time: +00 00:00:04.730734
COUNT (*) time: +00 00:00:07.868103
COUNT (1) beat COUNT (*) by +00 00:00:03.137369
COUNT (1) is leading COUNT (*) by +00 00:00:02.732932
Overall COUNT (1) beat COUNT (*) by +00 00:00:02.732932

Tom Kyte
March 26, 2010 - 2:48 pm UTC

neither "beat" the other - ever. The difference between the two is so small as to be natural, not statistically significant, meaningless.

count(*) beats count(*) sometimes. count(1) beats count(1) sometimes.


timing is useless for something like this (timing in sqlplus). You timed how long it took SQLPlus to paint the screen, you timed the network, you timed the server - including "wait time" (if someone else is doing something - you will WAIT for them).

Also, emptying the buffer cache - not smart for this one. You are trying to see if count(1) is faster/slower/same as count(*). You should be REMOVING anything that could time anything else. You would like to have a table with a lot of rows that is simple to evaluate (dba views - bad choice for this too). You would like to remove everything you can except for the count bit.


You have shown "nothing conclusive"

However, the fact is - count(1) is rewritten internally to be count(*) - to correct the BUG in the query that submits count(1). count(1) is wrong, count(*) is semantically correct.

about count(*)

A reader, September 23, 2010 - 10:36 am UTC

About using Count(*) -is explained clearly here
http://bix-erusadla.blogspot.com/

no recursion better than some

oracle-unix, November 18, 2010 - 5:55 pm UTC

Does the recursive calls mean a slight difference?  10.2.0.4:

SYS> select count(*) from dba_synonyms;

  COUNT(*)
----------
     20197


Execution Plan
----------------------------------------------------------
Plan hash value: 3261027237

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     1 |    20 |   222   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE      |        |     1 |    20 |            |          |
|*  2 |   HASH JOIN          |        | 20204 |   394K|   222   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL | USER$  |    86 |   344 |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS      |        | 20212 |   315K|   219   (2)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| OBJ$   | 20528 |   220K|   216   (1)| 00:00:03 |
|*  6 |     INDEX UNIQUE SCAN| I_SYN1 |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."OWNER#"="U"."USER#")
   5 - filter("O"."TYPE#"=5)
   6 - access("O"."OBJ#"="S"."OBJ#")


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

SYS>  select count(1) from dba_synonyms;

  COUNT(1)
----------
     20197


Execution Plan
----------------------------------------------------------
Plan hash value: 3261027237

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     1 |    20 |   222   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE      |        |     1 |    20 |            |          |
|*  2 |   HASH JOIN          |        | 20204 |   394K|   222   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL | USER$  |    86 |   344 |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS      |        | 20212 |   315K|   219   (2)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| OBJ$   | 20528 |   220K|   216   (1)| 00:00:03 |
|*  6 |     INDEX UNIQUE SCAN| I_SYN1 |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."OWNER#"="U"."USER#")
   5 - filter("O"."TYPE#"=5)
   6 - access("O"."OBJ#"="S"."OBJ#")


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

It seems consistent on repetition.  I was curious after I saw bug 10294904 (slow all_synonyms on 11.2.0.2)

Tom Kyte
November 19, 2010 - 9:21 am UTC

not sure what you mean by "Does the recursive calls mean a slight difference?"

slight difference between what and what?


Your Very First Reply.

Rajeshwaran, Jeyabal, November 19, 2010 - 3:22 pm UTC

Tom:

In your very first reply to this thread, you said

It only takes ONE CASE and I'll be wrong (but you'll never find it).

In which case you will be wrong?
Tom Kyte
November 20, 2010 - 5:17 am UTC

people were making the mistaken claim that "count(1) is better than/faster than count(*)"

I told them to prove me wrong - I said "no it isn't, count(1) is not better than, is not faster than count(*)". I told them to provide a reproducible test case - just one - that supported their view.


Difference between count(*) and count(1)

Khattak, November 24, 2010 - 1:32 am UTC

Hi Tom,

Here are some queries and there outputs are given ,please look it.
SQL> select count(*) from all_objects;

  COUNT(*)
----------
     49930

Elapsed: 00:00:04.08

SQL> select count(1) from all_objects;

  COUNT(1)
----------
     49930

Elapsed: 00:00:01.94
 
So, Tom please Tell me that can I say that Count(1) is faster/better then count(*), But if you say not then why there is difference in the timing of the above given queries?

Tom Kyte
November 24, 2010 - 8:54 am UTC

reverse the order of the queries, now which is faster. No you cannot say count(1) is faster - read this page (you should have been able to guess my response :) )

do you think perhaps that the buffer cache or operating system file cache MIGHT just have something to do with it?

do you think that perhaps the SECOND execution benefits from work performed by the first????

and don't use sqlplus timing, you are timing lots of things beyond the execution of the query. Use tkprof - it would have made this self evident.

do this to benchmark:

run both queries.
turn on tracing
run both queries a few times.
turn off tracing
get the tkprof

You'll see something like this:

select count(*) from all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6     25.52      26.18       3268     133983          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12     25.52      26.18       3268     133983          0           3
********************************************************************************
select count(1) from all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.16       0.17          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6     27.41      27.65       2805     133983          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12     27.57      27.82       2805     133983          0           3



They are just about the same - because internally count(1) is automagically rewritten as count(*) to fix the bug in your code (the use of count(1) is a bug in your code, count(*) is correct - you want to just count rows - which is easier than counting non-null occurrences of the number one)

count(*)

A reader, November 25, 2010 - 10:21 am UTC


Differenct Count(*) and Count(a.*)

BRT, December 30, 2010 - 8:31 am UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789



Hi tom,

I have been your fan since long time.
I could not explain following behavior to my colleague, while suggesting him to use Count(*).

SQL> SELECT count(*)
  2  FROM rgis_time_sheet_details rt;

  COUNT(*)
----------
   4196998

SQL> SELECT count(rt.*)
  2  FROM rgis_time_sheet_details rt;
SELECT count(rt.*)
                *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

What can be the difference between count(*) and count(rt.*) ? 

Tom Kyte
December 30, 2010 - 4:46 pm UTC

the difference is:

count(*) is documented, it returns the count of all rows in a result set.


count(alias.*) is meaningless - it doesn't make sense.

The * in count is not a reference to all of the columns, it is a special parameter that says "please count rows"

Further Clarification

michael, July 28, 2011 - 11:46 am UTC

Will count() ever return no_data_found? I have encountered in code the following:

begin
select count(*)
into ...
from ...
exception
when no_data_found then
...
end;

It was my understanding that the exception was unnecessary with count, in that it will ALWAYS return a number. Thanks
Tom Kyte
July 28, 2011 - 7:35 pm UTC

an aggregate without a group by always returns

at least one row
at most one row


the exception in this example, since the select has NO group by, will never happen.

ops$tkyte%ORA11GR2> select count(*) from dual where 1=0;

  COUNT(*)
----------
         0

ops$tkyte%ORA11GR2> 


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.