Ellis, I would have thought that if the two query plans are the same then the time taken to run the query would also be very similar. Perhaps the myth was born around the idea that * retrieved data from the base table using rowid lookups. Craig Munday Software Development Manager Giesecke & Devrient Australasia P/L, 32 Birnie Ave, Lidcombe 2141 Phone: +61 2 9646 6200 Fax: +61 2 9749 1678 Craig.Munday@xxxxxxxxxxxx http://www.gdaus.com.au -----Original Message----- From: Ellis R. Miller [mailto:sartre1@xxxxxxxxxxx]=20 Sent: Friday, 9 July 2004 4:47 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Difference between count(1) and count(*) I will alert the last dozen companies I have consulted for, immediately, as from all indications it sounds as if they may need to stop gathering high-level requirements and move boldly into the second phase: gathering requirements for development training and mentoring i.e. Part I, Successfully unzipping the J2SE 1.4.2 SDK and, Part II, how to find it in the file system (how do you know it really unzipped). -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of DENNIS WILLIAMS Sent: Thursday, July 08, 2004 11:09 AM 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 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------