question about count()
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: question about count()

  1. #1
    Join Date
    Jan 2002
    Posts
    33
    I have a table test:
    table test:
    COL1 COL2
    -------- ---------
    1 2
    2 3
    4

    if use following statement,
    SELECT count(1), count(rowid)
    FROM test;

    COUNT(1) COUNT(ROWID)
    --------- ------------
    3 3

    What does "1" stand for? I only know in this statement
    SELECT* FROM test ORDER BY 1 DESC;
    1 stands for the first column. But if use
    SELECT count(col1) FROM test;

    COUNT(COL1)
    -----------
    2

    looks like "1" in count(1) has different meaning with "1" in order by 1.

    Many thanks.


  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    u right,
    ""1" in count(1) has different meaning with "1" in order by 1"

    in count(1) - this is only 1 as digit.
    in order by 1 - this is number of fileld in select fld1, fld2 ... (select *) ...
    on witch u going to sort result(rows in select).

  3. #3
    Join Date
    Jan 2002
    Posts
    33
    Hi. Shestakov,

    Thanks.

    But I'm still confused. Why should we use SELECT count(1) FROM test?

    If there are more 10,000 records, using SELECT count(1) FROM table_name is faster than SELECT count(*) FROM table_name. Could you please tell me about count(1) is from what kind idea?



  4. #4
    Join Date
    Sep 2001
    Posts
    52

    Smile

    Hi

    Whenever you use "*" in the SELECT oracle will search for all the columns to get the count. If you use any integer or number for the count function it will not scan all the columns and that's why the result will be much faster.

    when you use count(col) it will ignore the NULL values in that column and exclude that row in the output of count function..




    Originally posted by jmniu
    Hi. Shestakov,

    Thanks.

    But I'm still confused. Why should we use SELECT count(1) FROM test?

    If there are more 10,000 records, using SELECT count(1) FROM table_name is faster than SELECT count(*) FROM table_name. Could you please tell me about count(1) is from what kind idea?



  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you should use count(*) there is no performance difference and if you have a PK then Oracle is clever enough to do a index fast full scan, if you are using 8i and it's fast and efficient

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Incorrect:
    Originally posted by DevangP
    Whenever you use "*" in the SELECT oracle will search for all the columns to get the count. If you use any integer or number for the count function it will not scan all the columns and that's why the result will be much faster.
    Correct:
    Originally posted by DevangP
    when you use count(col) it will ignore the NULL values in that column and exclude that row in the output of count function..
    Absolutely correct:
    Originally posted by pando
    you should use count(*) there is no performance difference and if you have a PK then Oracle is clever enough to do a index fast full scan, if you are using 8i and it's fast and efficient
    - Chris

    [Edited by chrisrlong on 02-13-2002 at 10:02 AM]
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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