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 = you wish) on your setup and forever dispel any myths: :-) =20 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 =20 Cheers,=20 =20 Vasan. -----Original Message----- From: Jared Still [mailto:jkstill@xxxxxxxxxx]=20 Sent: 08 July 2004 08:20 To: Oracle-L Freelists Subject: Re: Difference between count(1) and count(*) =20 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) ? >=20 > count(1) is supposed to be more efficient and faster. =20 Notice the qualifier: 'supposed to be' =20 Was the claim backed up by evidence? =20 Jared =20 =20 =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 ----------------------------------------------------------------- _________________________________________________________________________= __=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 -----------------------------------------------------------------