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
[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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.
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?