Re: Select count(*) in Oracle and MySQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 09 Oct 2007 10:02:53 -0700
Message-ID: <1191949360.256259@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

Received on Tue Oct 09 2007 - 12:02:53 CDT