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
