Re: Select count(*) in Oracle and MySQL

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 10 Oct 2007 10:02:36 +0200
Message-ID: <470c872f$0$233$e4fe514c@news.xs4all.nl>

<richard.drouillard_at_gmail.com> schreef in bericht news:1191940884.508757.257320_at_v3g2000hsg.googlegroups.com...
> 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
>

The BLOG you refer to does not recommend it for performance reasons, but for avoiding null values in columns. In that case, count(rowid) should give the same result as count(10) or count(*) or count(8)

Shakespeare Received on Wed Oct 10 2007 - 03:02:36 CDT