Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!o80g2000hse.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:06:14 -0700 Organization: http://groups.google.com Lines: 66 Message-ID: <1192021574.586756.258260@o80g2000hse.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> NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1192021574 20797 127.0.0.1 (10 Oct 2007 13:06:14 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 10 Oct 2007 13:06:14 +0000 (UTC) In-Reply-To: <1192020291.920830.122870@d55g2000hsg.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: o80g2000hse.googlegroups.com; posting-host=138.32.32.166; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.server:436163 X-Received-Date: Wed, 10 Oct 2007 09:06:14 EDT (text.usenetserver.com) 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