Diff between cout(*) and count(1)

[prav_saras]

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

[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.

[adewri]

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).

[Mr.Hanky]

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

[Mr.Hanky]

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

[DaPi]

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.

[DaPi]

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!