-
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
-
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 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.
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|