Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!o3g2000hsb.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.server Subject: Re: Select count(*) in Oracle and MySQL Date: Wed, 10 Oct 2007 06:36:28 -0700 Organization: http://groups.google.com Lines: 96 Message-ID: <1192023388.663171.116770@o3g2000hsb.googlegroups.com> References: <1190227221.320339.178380@50g2000hsm.googlegroups.com> <1191940884.508757.257320@v3g2000hsg.googlegroups.com> <1191949360.256259@bubbleator.drizzle.com> <470c859a$0$240$e4fe514c@news.xs4all.nl> <470c8a01$0$242$e4fe514c@news.xs4all.nl> <1192020291.920830.122870@d55g2000hsg.googlegroups.com> <1192021574.586756.258260@o80g2000hse.googlegroups.com> NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1192023388 8546 127.0.0.1 (10 Oct 2007 13:36:28 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 10 Oct 2007 13:36:28 +0000 (UTC) In-Reply-To: <1192021574.586756.258260@o80g2000hse.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: o3g2000hsb.googlegroups.com; posting-host=138.32.32.166; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.server:436167 X-Received-Date: Wed, 10 Oct 2007 09:36:28 EDT (text.usenetserver.com) On Oct 10, 8:06 am, "fitzjarr...@cox.net" wrote: > On Oct 10, 7:44 am, bhonaker wrote: > > > > > > > On Oct 10, 4:14 am, "Shakespeare" wrote: > > > > "Shakespeare" schreef in berichtnews:470c859a$0$240$e4fe514c@news.xs4all.nl... > > > > > "DA Morgan" schreef in bericht > > > >news:1191949360.256259@bubbleator.drizzle.com... > > > >> richard.drouill...@gmail.com wrote: > > > >>> On Sep 19, 2:40 pm, Occidental wrote: > > > >>>> I have a table with about 250M rows, implemented in both Oracle and > > > >>>> MySQL. > > > >>>> Select count(*) in MySQL is effectively instantaneous, presumably > > > >>>> because it accesses some internal count that is maintained by the > > > >>>> DBMS. The same query in Oracle takes about 6 minutes, pretty obviously > > > >>>> becasue it counts rows. The Oracle table should have been set up with > > > >>>> a primary key, but wasn't. The MySQL table has no primary key either. > > > >>>> Any comments? > > > > >>> Oracle recommends using count(rowid) instead of count(*) for > > > >>> performance reasons, I think you'll find this much faster. > > > > >>>http://www.thewellroundedgeek.com/2007/09/most-people-use-oracle-coun... > > > > >>> - Rich > > > > >> Oracle doesn't recommend it ... you do. And in doing so you are > > > >> incorrect. > > > > >> You should remove the recommendation from the website. > > > >> -- > > > >> Daniel A. Morgan > > > >> University of Washington > > > >> damor...@x.washington.edu (replace x with u to respond) > > > >> Puget Sound Oracle Users Group > > > >>www.psoug.org > > > > > In 11g, there seem to be differences in performance with count(*) and > > > > count(1), but only in rare cases like after adding a column to a view, > > > > IIRC. Have seen some article about that, I'll try to look it up (but maybe > > > > this WAS the quoted article). Count(rowid) is a bad idea for views BTW. > > > > > Shakespeare > > > > And here's the article. Not a perfomance issue, but still different > > > behaviour:http://www.pythian.com/blogs/627/oracle-11g-unexpected-difference-bet... > > > > Shakespeare > > > The blog entry Rich linked to is in fact his own posting; what is > > interesting is that in the blog he correctly identifies the book > > "Oracle Press Book: Oracle 10g: SQL" as the source, while here he > > attributes this "fact" to Oracle. > > > Brian- Hide quoted text - > > > - Show quoted text - > > I find no such attribution in that blog post. > > David Fitzjarrell- Hide quoted text - > > - Show quoted text - I stand corrected, the attribution is in a comment to the original by the author, however the text 'Oracle 10g: SQL' is not an offering from Oracle Press, it's published by Thomson Course Technology, and is authored by Joan Casteel. Interesting how he's changed the original quote from this: 'You should avoid using the asterisk (*) with the COUNT() function as it may take longer for COUNT() to return the result. Instead you should use a column in the table or the ROWID....' to this: "Most people use the oracle count function in the following way to count the number of rows in a table: select count(*) from my_table; While this does technically work, on larger tables it can be grossly inefficient to perform this query. " without a bit of proof to illustrate that claim. David Fitzjarrell