RE: Difference between count(1) and count(*)

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 9 Jul 2004 10:11:58 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEABF@bosmail00.bos.il.pqe>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Srinivasan Vasan Sent: Friday, July 09, 2004 4:46 AM
To: 'oracle-l_at_freelists.org'
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_at_churchill.com
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_at_LIFETOUCH.COM]=20 Sent: 08 July 2004 18:09
To: 'oracle-l_at_freelists.org'
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_at_lifetouch.com=20
I said it "looked" clear - Riddick

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Michael Brown Sent: Thursday, July 08, 2004 12:06 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Miller Sent: Thursday, July 08, 2004 9:40 AM
To: oracle-l_at_freelists.org
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_at_churchill.com] Sent: 08 July 2004 12:47
To: 'oracle-l_at_freelists.org'
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_at_cybcon.com]=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_at_freelists.org 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_at_freelists.org

put 'unsubscribe' in the subject line.

--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org put

'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Fri Jul 09 2004 - 09:10:02 CDT