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
