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
Received on Wed Oct 10 2007 - 21:53:37 CDT
