2 Replies Latest reply: May 1, 2012 1:36 PM by Gaff RSS

    SQL Query efficiency

    934344
      Can anyone tell me , is there any difference between count(*) and count(1)
      I believe count(1) is faster than count(*), as it is taking count for 1st column.
      Is there any other difference or my belief is not right.
      Can anyone plz explain it with example....
        • 1. Re: SQL Query efficiency
          JustinCave
          1) There is no difference in performance for any vaguely recent version of Oracle.
          2) If there ever is a difference, COUNT(*) would be the form that would be optimized. So if there ever is a difference COUNT(*) will be faster.
          3) COUNT(1) does not count the first column. It counts the literal number 1 for every row. You'd get the same behavior if you did COUNT('BadgerBadgerBadger') or COUNT( date '2012-01-15' ).
          4) If I see code that has a bunch of COUNT(1)'s, I generally assume that whoever wrote it is prone to believing random myths they've found on the internet rather than testing thing for themselves so I assume that the surrounding code is more likely to have bugs. Particularly if I'm doing a code review.

          Justin
          • 2. Re: SQL Query efficiency
            Gaff
            Don't be ridiculous. No one counts badgers in threes! (At least not in any recent version of Oracle). ;)


            Justin Cave wrote:
            3) COUNT(1) does not count the first column. It counts the literal number 1 for every row. You'd get the same behavior if you did COUNT('BadgerBadgerBadger')
            Justin