On Oct 4, 1:20 am, Akp <[email protected]> wrote: > count(1) is count(*) in disguise [post 7.x version of database] > > Please refer Tom Kyte for more > explanationhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1... > > On Oct 4, 12:03 am, Michael Moore <[email protected]> wrote: > > > > > 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-Hidequotedtext > > >> - > > > >> > > > - 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-Hidequoted 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- Hide quoted text - > > - Show quoted text -
As I posted previously the 10046 trace proves otherwise; yes, the query statement is transformed but the recursive SQL generated is different (please see my prior post showing this). 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
