Re: Select count(*) in Oracle and MySQL

On Oct 11, 7:38 am, bhonaker <bhona..._at_gmail.com> wrote:
> On Oct 10, 9:36 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Oct 10, 8:06 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Oct 10, 7:44 am, bhonaker <bhona..._at_gmail.com> wrote:
>
> > > > On Oct 10, 4:14 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
>
> > > > > "Shakespeare" <what..._at_xs4all.nl> schreef in berichtnews:470c859a$0$240$e4fe514c_at_news.xs4all.nl...
>
> > > > > > "DA Morgan" <damor..._at_psoug.org> schreef in bericht
> > > > > >news:1191949360.256259_at_bubbleator.drizzle.com...
> > > > > >> richard.drouill..._at_gmail.com wrote:
> > > > > >>> On Sep 19, 2:40 pm, Occidental <Occiden..._at_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..._at_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
>
> Not trying to pick nits here David, but two corrections: the book he
> refers to (or at least tries to) is "Oracle Database 10g: SQL" by
> Jason Price from Oracle Press, and also, he does not in any way
> attempt to quote the original in his blog post - he's paraphrasing
> (albeit hyperbolically). Later on in the comments section when
> challenged on his assertion he quotes the original text exactly (which
> is how I found the book online).
>
> Brian- Hide quoted text -
>
> - Show quoted text -

Thank you. When I searched for the same text the only offering presented to me was the one I referenced.

David Fitzjarrell Received on Thu Oct 11 2007 - 08:23:07 CDT