Re: Select count(*) in Oracle and MySQL

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:470c859a$0$240$e4fe514c_at_news.xs4all.nl...
>
> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
> news:1191949360.256259_at_bubbleator.drizzle.com...
>> richard.drouillard_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-count-function.html
>>>
>>> - 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
>> damorgan_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-between-count-and-count1

Shakespeare Received on Wed Oct 10 2007 - 03:14:38 CDT