RE: Difference between count(1) and count(*)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Jul 2004 10:11:58 -0400
I disagree. These don't look significantly different to me, with the
possible exception of MAX(rownum), which isn't even the same aggregate =
function anymore.
However, I ran each of the tests with autotrace on, and as expected, =
every execution did
the same number of consistent gets and had the same execution plan.
Here are my results on 8.1.7.4 on Solaris:
SQL> @test_count
SQL> select /*+ RULE */ count(*) from sys.source$;
COUNT(*)
----------
212502
Elapsed: 00:00:02.57
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7703 physical reads
0 redo size
493 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ count(*) from sys.source$;
COUNT(*)
----------
212502
Elapsed: 00:00:02.57
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7704 physical reads
0 redo size
493 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ max(rownum) from sys.source$;
MAX(ROWNUM)
-----------
212502
Elapsed: 00:00:02.69
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7704 physical reads
0 redo size
496 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ count(rownum) from sys.source$;
COUNT(ROWNUM)
-------------
212502
Elapsed: 00:00:02.70
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7705 physical reads
0 redo size
498 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ count(rowid) from sys.source$;
COUNT(ROWID)
------------
212502
Elapsed: 00:00:02.73
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7705 physical reads
0 redo size
497 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ count(obj#) from sys.source$;
COUNT(OBJ#)
-----------
212502
Elapsed: 00:00:02.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7705 physical reads
0 redo size
496 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ count(obj#) from sys.source$;
COUNT(OBJ#)
-----------
212502
Elapsed: 00:00:02.60
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7705 physical reads
0 redo size
496 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RULE */ count(source) from sys.source$;
COUNT(SOURCE)
-------------
212502
Elapsed: 00:00:02.72
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SOURCE$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7745 consistent gets
7705 physical reads
0 redo size
498 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
Here's the script that I ran:
set timing on
set autot on
spool test_count.lst
set echo on
select /*+ RULE */ count(*) from sys.source$;
select /*+ RULE */ count(*) from sys.source$;
select /*+ RULE */ max(rownum) from sys.source$;
select /*+ RULE */ count(rownum) from sys.source$;
select /*+ RULE */ count(rowid) from sys.source$;
select /*+ RULE */ count(obj#) from sys.source$;
select /*+ RULE */ count(obj#) from sys.source$;
select /*+ RULE */ count(source) from sys.source$;
spool off
Conclusion: With the RBO, it's LESS likely that there will be =
differences
between the various statements. That's cause the RBO is less =
intelligent
about identifying optimizations such as recognizing that it could use
an index on a column, if said column has a NOT NULL constraint. RBO =
will
tend to find the simple FTS execution plan in every case.
There REALLY is not difference betweeen count(*) and count(1) since at =
least 8.0.
It's known that there were differences in 7.3 and before. =20
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Srinivasan Vasan
Sent: Friday, July 09, 2004 4:46 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Difference between count(1) and count(*)
And if you are on 8.1.7.4, it still makes some difference if you are =
running
RBO as shown below:
Using Sys.Source$ for the test
Row Count using all columns
COUNT(*)
----------
1756877
Elapsed: 00:00:05.25
Row Count using a specific numbered column
COUNT(1)
----------
1756877
Elapsed: 00:00:05.50
Row Count using the pseudo-column rownum
MAX(ROWNUM)
-----------
1756877
Elapsed: 00:00:06.72
Row Count using the ROWID column
COUNT(ROWID)
------------
1756877
Elapsed: 00:00:05.67
Counting a Not-NULL column
COUNT(OBJ#)
-----------
1756877
Elapsed: 00:00:05.56
Counting a NULL column
COUNT(SOURCE)
-------------
1756877
Elapsed: 00:00:05.63
Cheers,=20
Vasan (x5707)=20
Mailpoint 28
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Vasan Srinivasan * 020 8313 5707
Infrastructure Service Manager * 020 8313 5646
Oracle Technologies
Churchill Insurance, IT Department
Purple Floor, Phase 1, Churchill Court
1 Westmoreland Road,
Bromley, Kent, BR1 1DP.
* Vasan.Srinivasan@xxxxxxxxxxxxx
Mobile * 07710 154 987
http://oratech
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Views Presented here are not necessarily the views
of my Employer
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
-----Original Message-----
From: DENNIS WILLIAMS [mailto:DWILLIAMS@xxxxxxxxxxxxx]=20
Sent: 08 July 2004 18:09
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Difference between count(1) and count(*)
In the first edition of Corrigan and Gurry's book Oracle Performance =
Tuning
(1993), which covers Version 6 (and Version 7 which was just coming =
out),
they state that they have conducted tests and count(*) consistently runs
between 15 and 20 percent faster than count(1). So those of you still =
doing
new development on Version 6 will want to take note of this.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx=20
I said it "looked" clear - Riddick
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Michael Brown
Sent: Thursday, July 08, 2004 12:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Difference between count(1) and count(*)
If I remember what Tom Kyte said about it correctly, there was a version
where it did make a difference (6, early 7 maybe) and thus a myth was
born. According to Tom, the parser is now coded so that count(1) and
count(*) use exactly the same code path so any differences you see
between the two would occur on multiple runs of the same statement, i.e.
they are caused by external events.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Peter Miller
Sent: Thursday, July 08, 2004 9:40 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Difference between count(1) and count(*)
1,749,951 rows returned in every case
count(rowid) - approx 3.2 secs
count(obj#) - approx 3.5 secs
count(1) - approx 3.6 secs
count(*) - approx 3.6 secs
count(rownum)- approx 4.6 secs
count(source)- approx 8.6 secs
-----Original Message-----
From: Srinivasan Vasan [mailto:Vasan.Srinivasan@xxxxxxxxxxxxx]
Sent: 08 July 2004 12:47
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Difference between count(1) and count(*)
Try the following test (using your own table instead of sys.source$ if =
=3D
you
wish) on your setup and forever dispel any myths: :-)
=3D20
set trimspool on
set timing on
spool test_count.lst
Prompt Using Sys.Source$ for the test
Prompt
Prompt Row Count using all columns
select count(*) from sys.source$;
Prompt Row Count using a specific numbered column
select count(1) from sys.source$;
Prompt Row Count using the pseudo-column rownum
select max(rownum) from sys.source$;
Prompt Row Count using the ROWID column
select count(rowid) from sys.source$;
Prompt Counting a Not-NULL column
select count(obj#) from sys.source$;
Prompt Counting a NULL column
select count(source) from sys.source$;
spool off
set timing off
set trimspool off
=3D20
Cheers,=3D20
=3D20
Vasan.
-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxxx]=3D20
Sent: 08 July 2004 08:20
To: Oracle-L Freelists
Subject: Re: Difference between count(1) and count(*)
=3D20
On Wed, 2004-07-07 at 23:28, Lyndon Tiu wrote:
> On Thu, 8 Jul 2004 11:27:28 +0530 oracle-l@xxxxxxxxxxxxx wrote:
> > Dear All,
> > Can you let me know the difference between count(*) and count(1) ?
>=3D20
> count(1) is supposed to be more efficient and faster.
=3D20
Notice the qualifier: 'supposed to be'
=3D20
Was the claim backed up by evidence?
=3D20
Jared
=3D20
=3D20
=3D20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
________________________________________________________________________
_=3D
__=3D20
This email and any attached to it are confidential and intended only for
=3D the individual or entity to which it is addressed. If you are not =
the
=3D intended recipient, please let us know by telephoning or emailing =
the
sender. =3D You should also delete the email and any attachment from =
your
systems and =3D should not copy the email or any attachment or disclose
their content to any =3D other person or entity. The views expressed =
here
are not necessarily those of Churchill Insurance Group plc or its
affiliates or subsidiaries. Thank =3D you.=3D20
Churchill Insurance Group plc. Company Registration Number - 2280426.
England.=3D20
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.=3D20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
_________________________________________________________________________=
__=20
This email and any attached to it are confidential and intended only for =
the
individual or entity to which it is addressed. If you are not the =
intended
recipient, please let us know by telephoning or emailing the sender. =
You
should also delete the email and any attachment from your systems and =
should
not copy the email or any attachment or disclose their content to any =
other
person or entity. The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank =
you.=20
Churchill Insurance Group plc. Company Registration Number - 2280426.
England.=20
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.=20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
