Re: Select count(*) in Oracle and MySQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 08 Oct 2007 09:49:23 -0700
Message-ID: <1191862150.770968@bubbleator.drizzle.com>

Mladen Gogala wrote:
> On Wed, 19 Sep 2007 11:40:21 -0700, Occidental 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?

>
> You can do that in oracle as well. In order to do that, you should set up
> a trigger that fires after insert, for each row and increments a value
> stored in another table by one. Of course, the effect on the concurrency
> will be devastating as only a single transactio will be able to perform
> an insert at any given time. Transactions will serialize trying to acquire
> transaction lock on the row count.
> On top of that, all applications which count records using "select
> count(*)" that I've seen in my few weeks of practicing Oracle have serious
> design issues. I don't know why do you need that count so quickly, but
> most likely, it's a bad design in both databases.

The OP doesn't even need to work that hard to duplicate mySQL.

SELECT num_rows FROM user_tables WHERE table_name =

is a close approximation of instantaneous.

-- 
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 Mon Oct 08 2007 - 11:49:23 CDT