DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Diff between cout(*) and count(1)

  1. #1
    Join Date
    Oct 2002
    Posts
    109

    Diff between cout(*) and count(1)

    Hi,
    What is the diference between
    select count(*) from emp; and select count(1) from emp;
    both will return the same number,then what is difference between these two?

    regards

    praveen

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The major difference is that * is in different places on different keyboards whereas 1 tends to stay put. slimdave has pointed out that 8 and 9 are close to the ( and ) characters, so count(9) is probably the most efficient.

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by DaPi
    The major difference is that * is in different places on different keyboards whereas 1 tends to stay put. slimdave has pointed out that 8 and 9 are close to the ( and ) characters, so count(9) is probably the most efficient.
    I think there is a tie between count(9) and count(0).
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    I read somewhere a long time ago that using a number instead of a splatt was more effecient. I forgot how or why, let's do and explain plan.

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    SQL> set autotrace on
    SQL> select count(*) from sample;

    COUNT(*)
    ----------
    276858


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 SORT* (AGGREGATE) :Q530900
    0

    3 2 INDEX* (FAST FULL SCAN) OF 'XPKSAMPLE' (UNIQUE) (Cost= :Q530900
    116 Card=274926) 0



    2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
    ROM (SELECT /*+ INDEX_RRS(A2 "XPKSAM

    3 PARALLEL_COMBINED_WITH_PARENT


    Statistics
    ----------------------------------------------------------
    63 recursive calls
    3 db block gets
    1395 consistent gets
    1283 physical reads
    800 redo size
    381 bytes sent via SQL*Net to client
    499 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL> select count(1) from sample;

    COUNT(1)
    ----------
    276858


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 SORT* (AGGREGATE) :Q531100
    0

    3 2 INDEX* (FAST FULL SCAN) OF 'XPKSAMPLE' (UNIQUE) (Cost= :Q531100
    116 Card=274926) 0



    2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
    ROM (SELECT /*+ INDEX_RRS(A2 "XPKSAM

    3 PARALLEL_COMBINED_WITH_PARENT


    Statistics
    ----------------------------------------------------------
    20 recursive calls
    4 db block gets
    1391 consistent gets
    1280 physical reads
    828 redo size
    381 bytes sent via SQL*Net to client
    499 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by adewri
    I think there is a tie between count(9) and count(0).
    NO! on my Swiss keyboard the ( ) are above 8 and 9.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    MH, those plans look identical to me. I suspect any difference in the stats is due to one being run after the other (N.B. the number of recursive calls).

    P.S. (added later) I've just noticed that the count(1) is changed to count(*) in the plan!
    Last edited by DaPi; 12-18-2003 at 10:43 AM.

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