Re: Select count(*) in Oracle and MySQL

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 11 Oct 2007 14:18:11 +0200
Message-ID: <470e1498$0$235$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1192071204.229371_at_bubbleator.drizzle.com...

> fitzjarrell_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

>
> And if I may be allowed to present a dose of reality to this thread:
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245
> -- 
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

The invalidated view still stands....

Shakespeare
(What's in a count?) Received on Thu Oct 11 2007 - 07:18:11 CDT