Skip to Main Content
  • Questions
  • Difference between count(1) and count(*)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, kiran.

Asked: July 09, 2002 - 11:55 am UTC

Last updated: November 12, 2003 - 8:01 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom,

Its a nice feeling to post a question in this web site for the first time (Thank God ! I am able to post a question after so much of wait !!).

I have a question, please answer it or atleast let me know some URL's where I can do some study regarding this.

What is the difference between

1) SELECT COUNT(*) FROM ALL_TABLES;
and
2) SELECT COUNT(1) FROM ALL_TABLES;

The following are statistics with query 1 [count(*)]

Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
23230 consistent gets
0 physical reads
0 redo size
539 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

The following are statistics with query 1 [count(*)]

Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
23230 consistent gets
0 physical reads
0 redo size
540 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


I am really unable to find any difference between the statistics.
Also, the execution paths are absolutely same
(Though, I cannot see the execution path for a query using Dictionary objects, I have run similar query against one user table).

Please help me in knowing which is faster and the tuning implications. Also, please let me know whether is there a difference in which both are executed. ?

Thanks a lot
Srirama Kiran Kumar

and Tom said...

bummer that you waited so long when a search for:

count(*) count(1)

on this site would have found an article entitled:

difference between count(1) and count(*)

that immediately would have answered this question

Rating

  (16 ratings)

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

Comments

A reader, July 09, 2002 - 3:31 pm UTC


Say it ain't so!

Mark A. Williams, July 09, 2002 - 3:57 pm UTC

"A Reader" rates it 1 star...

Why can't people be bothered to use the search facility, do you think? It isn't exactly hard to find or use... same reason they don't read the Oracle docs?

Maybe you should put a link to the Oracle docs online on your main page? But, would it help?

Anyway, another unsolicited $0.02...

- Mark

Perfect Answer

Jan, July 09, 2002 - 8:26 pm UTC

to reader 1 and others like he:

This page is also a kind of Information System. It means - System about information, not just a storage of anything. Search before. If something is already responded, don`t ask again.
Use 'modular approach' - reusability.

Thank you Tom

Kiran, July 09, 2002 - 11:37 pm UTC

Hi Tom,

I have went through the series of Q/A's resulted due to the search which you suggested. I have found it very useful. Also, I realize that Searching the forum is important before I post.
I expected a different explaination, i.e in terms of the way count(*) and count(1) are executed i.e how blocks are read in both cases. Please do respond if you feel its worth.

Thanks
kiran

Tom Kyte
July 10, 2002 - 7:26 am UTC

Ok, read this

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

which is the article "difference between count(1) and count(*)".

It starts with:

nothing, they are the same, incur the same amount of work -- do the same thing,
take the same amount of resources.



they are in fact *the same thing*

Some comments

Karl, July 10, 2002 - 9:21 am UTC

Tom,

Thank you for this very useful site.

As for the Original question, I think they were more interested in having the pleasure of posting it than really asking the question itself.

However, it's needless to say that some myths are almost unbreakable. And some are even written down and signed off by Oracle, for example in Oracle Press books. So, don't blame us poor mortals for being confused sometimes.

For example, Mr. Jason Couchman in his Oracle8i Certification Exam Guide objectively states:

- "Use COUNT(1) instead of COUNT(*) because of its performance benefits."
- "The ORDER BY columns have to be in the SELECT clause."
- "The password is encrypted and stored in the Oracle database"

... among others.


Tom Kyte
July 10, 2002 - 12:24 pm UTC

VBG

they are of course all wrong (and the second one is trivial to prove wrong, cannot imagine how that got past the editors!)

A reader, July 10, 2002 - 9:40 am UTC

In the above article, there is an unresponded post by Dulimin in China where he posted trace details of count(1) and count(*) on 8.1.7. Curious to know your response to it. Thanks.

Tom Kyte
July 10, 2002 - 12:39 pm UTC

write it off to a side effect of the cartesian product.


How is password stored in the database?

A reader, July 10, 2002 - 3:47 pm UTC

This is related to Karl's third observation.... How is the password stored then, I mean what is wrong in that statement?

Tom Kyte
July 10, 2002 - 3:50 pm UTC

It is not encrypted. Encrypted implies there would be a decrypt. Encrypt implies there is a key that would unlock the passwords -- there is not.

It is a one way message digest, very very different.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:95412348059 <code>
for example.

A reader, July 10, 2002 - 3:50 pm UTC

Hi Tom,

I've seen in varous environments where, when a count(1) was changed to count(*), the performance improved dramatically, sometimes by a hundred fold. What would be the possible reasons for this?

thanks

Tom Kyte
July 10, 2002 - 4:12 pm UTC

i give up, use count(*) ;)



It is what I use.

The old "myth" was the count(1) was faster, better -- that you should avoid count(*).

Use count(*), it is more appropriate anyway.




Gee !

A reader, July 10, 2002 - 4:21 pm UTC

I don't believe... no slam on this last one ?

?

A reader, July 10, 2002 - 4:22 pm UTC

Didn't he read the top of the page ?

How about this?

Mr. 420, July 10, 2002 - 5:28 pm UTC

I am pretty sure that SELECT COUNT(ROWID) FROM a_very_large_tbl is faster than using COUNT(*). I would say, I am 99% sure. Tom, can you be kind enough to confirm this?

RE: How about this?

Mark A. Williams, July 10, 2002 - 8:53 pm UTC

PMFJI...

In regard to count(rowid) - just benchmark it!

I created a table (t) as select * from all_objects, and then repeated insert into t select * from t until I had 3330304 rows in the table.

Here's the relevant trace/tkprof results:

select count(*)
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.34 13.88 40967 42239 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.35 13.88 40967 42240 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23

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
db file scattered read 2605 0.07 10.44
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 5.99 5.99
********************************************************************************

select count(rowid)
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.22 13.71 40967 42239 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.24 13.71 40967 42240 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23

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
db file scattered read 2605 0.07 10.29
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 10.26 10.26
********************************************************************************

My laptop hard drive isn't terribly fast, so ignore the waits. The thing is, 13.88 and 13.77 don't seem too different. Do the same benchmark. Make the table bigger if you want. Play with it. Have fun. See what happens.

- Mark

Mark's response:

Mr. 420, July 24, 2002 - 9:50 am UTC

Mark, Thank you very much for that benchmark test, and clearing my doubts. I have to learn how tkprof/trace utilities work. They seem very useful! Thanks again.

PMFJI...????????

A reader, July 24, 2002 - 9:51 am UTC

Mark, what does that mean?

RE: Mark, what does that mean?

Mark A. Williams, August 17, 2002 - 3:43 pm UTC

PMFJI == (P)ardon (M)e (F)or (J)umping (I)n

Since I jumped into the middle of the thread, I was trying to be a bit polite about it... and this response is only a month late, but I just read the question!

HTH (Hope This Helps)...

- Mark

count(1) and count(field)....why NULL is being counted?

Bhavani, November 12, 2003 - 8:01 am UTC

Hi Tom,

ops@florida> create table t (f integer);

Table created.

ops@florida> insert into t values(1);

1 row created.


Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE



ops@florida> set autotrace off
ops@florida> insert into t values(1);

1 row created.

ops@florida> insert into t values(2);

1 row created.

ops@florida> insert into t values(3);

1 row created.

ops@florida> select count(1) from t;
/c cls COUNT(1)
----------
4

ops@florida> select count(f) from t;
/c cls COUNT(F)
----------
4

ops@florida> insert into t values(NULL);

1 row created.

ops@florida> commit;

Commit complete.

ops@florida> select count(f) from t;
/c cls COUNT(F)
----------
4

ops@florida> select count(1) from t;
/c cls COUNT(1)
----------
5

Why "select count(f) from t;" is not counting the row with NULL?

Thanks
Bhavani

Tom Kyte
November 12, 2003 - 10:37 am UTC

that is the very essence of count

count(*) = count all rows

count( <expression> ) = count all NON NULL rows

count( distinct <expression> ) = count unique non null values


count(1) is the wrong way to code that, count(*) is correct and proper (btw)...