Choc, On this example the difference might be negligible but on a larger table it might not. If there is a difference in performance, I'd like to understand it. If there is no difference in performance then why bother creating confusion by using count(1)? The fact that they use the 1 implies that the 1 does something. Furthermore it implies that count(2) would do something different than count(1).
Mike On Sat, Oct 1, 2011 at 12:08 AM, choc101 <[email protected]> wrote: > Well what's 20% of a fraction of time anyways? It's all negligible to > me and it's not worth the time to debate this topic. I have come > across threads about people debating both sides to the death...why > bother? Pick one you like and use it IMO. > > 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 > > -- > 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
