Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    13

    Unanswered: difference in count(*) and count(1)

    what is difference between count(*) and count(1) ;
    is count(*)create any performance degradation in running the query particularly when table contain more records say in lakhs;
    my table is having 4 columns , so even if i give count(1),count(*),count(2),count(10) it will give same result;
    can anybody give clarification .
    thanks in advance

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Think of it this way:

    query your table that has four rows of data in four columns:
    Code:
    SELECT * FROM my_table;
    returns:
    Code:
    col1data col2data col3data col4data
    col1data col2data col3data col4data
    col1data col2data col3data col4data
    col1data col2data col3data col4data
    
    4 rows selected.
    but this query (with same rows of "actual" data):
    Code:
    SELECT 1 FROM my_table;
    returns:
    Code:
    1
    1
    1
    1
    
    4 rows selected.
    Basically, if you do a COUNT(*), Oracle returns an entire row of data and then counts it as 1, then goes to the next row and counts that as 2, and so on.

    A COUNT(1) would "count" a "1" for row 1, a "1" for row 2, and so on. Anything you put in the COUNT() function work the same. However, if you use a column name as the parameter, COUNT will return a count of NON-NULL values of that column.

    Maybe more than you'd like to know, but hope this helps...
    Last edited by joebednarz; 07-18-06 at 15:04.
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joebednarz
    Think of it this way ...

    Basically, if you do a COUNT(*), Oracle returns an entire row of data and then counts it as 1, then goes to the next row and counts that as 2, and so on.
    disclaimer: i am not an oracle guy

    but i would be surprised if oracle were not optimized enough to use any available index instead of actually counting rows

    of course, you did say "think of it this way" so maybe you knew that this might not actually be the way it works



    good point about COUNT(column) counting only non-nulls
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2006
    Posts
    132
    Check this out: http://tinyurl.com/muuxj

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Since Oracle 7, count(*) and count(1) are the exact same. Oracle knows that you want a count of ALL rows. To do this, it need to perform a full table scan. If you do a select count(col1), if col1 has an index, it will use it to count the rows. If it doesn't, then it will also do a FTS to findout the count.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by beilstwh
    Since Oracle 7, count(*) and count(1) are the exact same.
    I wouldn't be surprised if they were the same in earlier versions too. I distinctly remember back in Oracle 6 days someone telling me you could COUNT(primary key column) and it would avoid a full table scan, which sounded good until I tested it and found it made no difference.

    Essentially COUNT(*) avoids the step of replacing whatever literal you entered with '*'. Also COUNT(1) would make you look like someone who believed performance myths without testing them, and I worry whenever I come across it in code because I wonder what else could be in there.
    Last edited by WilliamR; 07-20-06 at 19:25.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by WilliamR
    .. I wonder what else could be in there.
    Rats? Bugs? Aliens?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i still cannot believe oracle would do a full table scan if there is an index that it can count instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oracle will choose an Index fast full scan if its stats say its quicker otherwise it will do the FTS. NOTE an index fast full scan isnt always quicker if for example your index has most of the columns in the table or say your index is badly fragmented.

    Alan

  10. #10
    Join Date
    Feb 2005
    Posts
    29
    This url below would give you some idea as well (or would it add to the confusion ) !

    http://asktom.oracle.com/pls/ask/f?p...:1156159920245
    Last edited by sashish4529; 07-19-06 at 12:37.

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Littlefoot
    Rats? Bugs? Aliens?
    ...hints, dynamic SQL, Cursor For loops with commit intervals...

    Oracle-WTF.blogspot.com

  12. #12
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Guess I need to keep up with the times... I do remember 7.x operating in the manner I mentioned, but apparently there has been a few changes in the database since then... Great discussion all. I stand corrected.
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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