count(*) vs count(1)

[jm]

does anyone know the difference between count(*) and count(1). I heard count(1) has better performance.

[Kishore]

Count(*) picks all the rows on the table while performing count where as count(1) just takes rowID to perform count which is obviously quick. Some one else may elaborate more !

[rcaballe]

It's right and pick UPLOAD the entire record to SGA wich could be (and is) very expensive.

[jm]

thanks, kishore,

basically, we can put in any number to get the number of rows.
how about other nubmer e.g. count(2) it will be the same as (*) use the whole row?

jim

[lnr]

Hi
If u try to execute queries with
count(*) ,count(1) and count(rowid) execution time is different i have tried it .
I dont know much in depth logically ,if any one knows please expalin it i will be thankful for it

[jmodic]

[QUOTE][i]Originally posted by rcaballe [/i]
[B]It's right and pick UPLOAD the entire record to SGA wich could be (and is) very expensive. [/B][/QUOTE]

There are never *rows* that are loaded into the SGA, the smalest unit of I/O are *database blocks*! So as far as buffer pool of SGA is concerned, there is no difference between count(*), count(1), count(null) or count(whatever). I/O load is always the same.

The only difference could be parsing time, hovewer I've heard that with newer versions of Oracle even this makes no difference at all.

This is more like an academic question, performance-vise there is no difference.

[ad]

Try count(PK_column), this will make a little boost on the performance compare to count(*).

AD

[jmodic]

[QUOTE][i]Originally posted by ad [/i]
[B]Try count(PK_column), this will make a little boost on the performance compare to count(*).

AD [/B][/QUOTE]
It may boost performance or it may also slow it down considerably. It depends on quite a few factors.

Generaly, if you use cost based optimizer and if you provide it with accurate enough statistics there should be no difference at all because the execution plan will be the same for all cases.

[chrisrlong]

JModic is right as usual. Count(*) and Count(1) are completely equivalent. Oracle is going to use the quickest method it can to count the resultset, meaning that count(*) from table will usually use the PK index because it's faster than hitting the table. However, if you are doing a complex statement first, it will simply count the resulting rows in the resultset - it does not matter what column you tell it to count , as long as it is already in the resultset (SELECT list) and you don't put a DISTINCT in there :) But basically, (*) or (1) are preferable because if you don't use DISTINCT, then you are really not counting that column, so why confuse the issue.

- Chris

[rcaballe]

Sorry, you are all right :)
I just contribute a rumor I heard, I promise not to do it again ;)
Anyway, searching in Metalink I've found there are a lot of bugs with count(*), Could be this a reason not to use it?
:D