A co-worker of mine said: I don’t think it matters, oracle transforms count(1) to count(*).
You can run a 10053 optimizer trace - CNT: Considering count(col) to count(*) on query block SEL$1 (#0) ************************* Count(col) to Count(*) (CNT) ************************* CNT: COUNT() to COUNT(*) done. … Final query after transformations:******* UNPARSED QUERY IS ******* SELECT COUNT(*) "COUNT(*)" FROM… Thoughts? On Mon, Oct 3, 2011 at 10:27 AM, Michael Moore <[email protected]>wrote: > Thanks David! > > > On Mon, Oct 3, 2011 at 9:07 AM, ddf <[email protected]> wrote: > >> >> >> On Sep 30, 10:42 am, Michael Moore <[email protected]> wrote: >> > David, >> > What conclusion can be drawn from your numbers. The Plan Hash values are >> > identical. Does this mean that the database essentially sees these as >> the >> > same query? There are differences, but could these differences be due to >> > other factors? >> > >> > Mike >> > >> > >> > >> > On Fri, Sep 30, 2011 at 10:31 AM, ddf <[email protected]> wrote: >> > >> > > On Sep 30, 8:42 am, Michael Moore <[email protected]> wrote: >> > > > I saw a demonstration somewhere that they are functionally 100% >> equal. >> > > > No performance difference. >> > > > Mike >> > >> > > > On Thu, Sep 29, 2011 at 11:53 PM, choc101 <[email protected]> >> wrote: >> > > > > As MM said they are the same, but the argument with these two >> > > > > approaches is always seems to be about which one runs faster. If I >> > > > > recall correctly count(*) is supposed to be faster than count(1) >> by >> > > > > about 20% or so, but I'm not about to get into how or why. I'm >> sure >> > > > > you could pull up some threads on ask tom that will explain this >> in >> > > > > all the detail you need or you could use TKPROF with tracing >> turned on >> > > > > and conduct your own test. Personally I just use count(*) as a >> > > > > standard and have never found any disadvantage to this. >> > >> > > > > On Sep 29, 2:22 am, PUNEET <[email protected]> wrote: >> > > > > > What is the difference between count(*) and count(1)??? >> > >> > > > > > I tried to search on that but not able to get the exact >> difference.. >> > >> > > > > > Thanks >> > >> > > > > > Puneet >> > >> > > > > -- >> > > > > You received this message because you are subscribed to the Google >> > > > > Groups "Oracle PL/SQL" group. >> > > > > To post to this group, send email to >> [email protected] >> > > > > To unsubscribe from this group, send email to >> > > > > [email protected] >> > > > > For more options, visit this group at >> > > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text >> - >> > >> > > > - Show quoted text - >> > >> > > SQL> >> > > SQL> select count(*) >> > > 2 from test; >> > >> > > COUNT(*) >> > > ---------- >> > >> > > 11046 >> > >> > > Elapsed: 00:00:00.07 >> > >> > > Execution Plan >> > > ---------------------------------------------------------- >> > > Plan hash value: >> > > 3467505462 >> > >> > > ------------------------------------------------------------------- >> > > | Id | Operation | Name | Rows | Cost (%CPU)| Time >> > > | >> > > ------------------------------------------------------------------- >> > > | 0 | SELECT STATEMENT | | 1 | 13 (8)| 00:00:01 >> > > | >> > > | 1 | SORT AGGREGATE | | 1 | | >> > > | >> > > | 2 | TABLE ACCESS FULL| TEST | 11046 | 13 (8)| 00:00:01 >> > > | >> > > ------------------------------------------------------------------- >> > >> > > Note >> > > ----- >> > > - dynamic sampling used for this >> > > statement >> > >> > > Statistics >> > > ---------------------------------------------------------- >> > > 5 recursive >> > > calls >> > > 0 db block >> > > gets >> > > 91 consistent >> > > gets >> > > 42 physical >> > > reads >> > > 0 redo >> > > size >> > > 210 bytes sent via SQL*Net to >> > > client >> > > 246 bytes received via SQL*Net from >> > > client >> > > 2 SQL*Net roundtrips to/from >> > > client >> > > 0 sorts >> > > (memory) >> > > 0 sorts >> > > (disk) >> > > 1 rows >> > > processed >> > >> > > SQL> >> > > SQL> select count(1) >> > > 2 from test; >> > >> > > COUNT(1) >> > > ------------ >> > >> > > 11046 >> > >> > > Elapsed: 00:00:00.21 >> > >> > > Execution Plan >> > > ---------------------------------------------------------- >> > > Plan hash value: >> > > 3467505462 >> > >> > > >> --------------------------------------------------------------------------- >> > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| >> > > Time | >> > > >> --------------------------------------------------------------------------- >> > > | 0 | SELECT STATEMENT | | 1 | 12 | 13 (8)| >> > > 00:00:01 | >> > > | 1 | SORT AGGREGATE | | 1 | 12 | >> > > | | >> > > | 2 | TABLE ACCESS FULL| TEST | 11046 | 129K| 13 (8)| >> > > 00:00:01 | >> > > >> --------------------------------------------------------------------------- >> > >> > > Note >> > > ----- >> > > - dynamic sampling used for this >> > > statement >> > >> > > Statistics >> > > ---------------------------------------------------------- >> > > 27 recursive >> > > calls >> > > 0 db block >> > > gets >> > > 93 consistent >> > > gets >> > > 0 physical >> > > reads >> > > 0 redo >> > > size >> > > 231 bytes sent via SQL*Net to >> > > client >> > > 246 bytes received via SQL*Net from >> > > client >> > > 2 SQL*Net roundtrips to/from >> > > client >> > > 0 sorts >> > > (memory) >> > > 0 sorts >> > > (disk) >> > > 1 rows >> > > processed >> > >> > > SQL> >> > >> > > David Fitzjarrell >> > >> > > -- >> > > You received this message because you are subscribed to the Google >> > > Groups "Oracle PL/SQL" group. >> > > To post to this group, send email to [email protected] >> > > To unsubscribe from this group, send email to >> > > [email protected] >> > > For more options, visit this group at >> > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - >> > >> > - Show quoted text - >> >> The difference in physical reads is due to running these one after the >> other and basically returning the same results; the number of >> recursive sql calls increases due to differences like this: >> >> select count(*) From dual; >> 1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=22 >> us cost=2 size=0 card=1) >> select count(1) from dual; >> 1 1 1 TABLE ACCESS CLUSTER LIBRARY$ >> (cr=3 pr=0 pw=0 time=3164 us cost=2 size=44 card=1) >> 1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 >> pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 3) >> select count(rowid) from dual; >> 1 1 1 TABLE ACCESS CLUSTER LIBRARY$ >> (cr=3 pr=0 pw=0 time=263 us cost=2 size=44 card=1) >> 1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 >> pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 3) >> >> The plan remains the same but the internal workings change that can >> cause excessive internal processing for repeated executions of the >> same query. >> >> Oracle is optimized for count(*) according to the 10046 trace files. >> >> >> David Fitzjarrell >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
