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
