Path: text.usenetserver.com!out01b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!d55g2000hsg.googlegroups.com!not-for-mail
From:  bhonaker <bhonaker@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Select count(*) in Oracle and MySQL
Date: Wed, 10 Oct 2007 12:44:51 -0000
Organization: http://groups.google.com
Lines: 57
Message-ID: <1192020291.920830.122870@d55g2000hsg.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>
NNTP-Posting-Host: 65.220.16.62
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1192020292 31018 127.0.0.1 (10 Oct 2007 12:44:52 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 10 Oct 2007 12:44:52 +0000 (UTC)
In-Reply-To: <470c8a01$0$242$e4fe514c@news.xs4all.nl>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: d55g2000hsg.googlegroups.com; posting-host=65.220.16.62;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.server:436161
X-Received-Date: Wed, 10 Oct 2007 08:44:52 EDT (text.usenetserver.com)

On Oct 10, 4:14 am, "Shakespeare" <what...@xs4all.nl> wrote:
> "Shakespeare" <what...@xs4all.nl> schreef in berichtnews:470c859a$0$240$e4fe514c@news.xs4all.nl...
>
>
>
>
>
> > "DA Morgan" <damor...@psoug.org> schreef in bericht
> >news:1191949360.256259@bubbleator.drizzle.com...
> >> richard.drouill...@gmail.com wrote:
> >>> On Sep 19, 2:40 pm, Occidental <Occiden...@comcast.net> 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