Re: Select count(*) in Oracle and MySQL

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.

-- 
http://www.mladen-gogala.com