count(*) vs count(1)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: count(*) vs count(1)

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    245
    does anyone know the difference between count(*) and count(1). I heard count(1) has better performance.


  2. #2
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    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 !
    Thanks
    Kishore Kumar

  3. #3
    It's right and pick UPLOAD the entire record to SGA wich could be (and is) very expensive.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  4. #4
    Join Date
    Nov 2000
    Posts
    245
    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

  5. #5
    Join Date
    Jan 2001
    Posts
    60
    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


    html code is off

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [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?

  7. #7
    Join Date
    Dec 2000
    Posts
    28
    Try count(PK_column), this will make a little boost on the performance compare to count(*).

    AD

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  10. #10
    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
    Ramon Caballero, DBA, rcaballe@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width