Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Difference between count(1) and count(*)

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

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 9 Jul 2004 10:48:37 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DDE5@usahm018.exmi01.exch.eds.com> 





At last someone who bothered to post explain plans to show that the
comparisons were valid tests!





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



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
Sent: Friday, July 09, 2004 10:12 AM


To: oracle-l_at_freelists.org


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




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





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:45:46 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US