Re: Select count(*) in Oracle and MySQL

On Wed, 19 Sep 2007 12:54:24 -0700, Occidental wrote:

> I need the count(*) because I want to know how many rows there are in
> the table.
> I am not, as the other contributor to the thread implied, trying to
> compare Oracle to MySQL, I am simply interested in determining whether
> the long runtime of the Oracle query is normal or reflects some error in
> the installation. If MySQL can do it, why not the Oracle? It seems
> rather absurd that a Database Management System does not know at any
> given time how many rows there are in the tables it "manages".

There is nothing preventing you from creating a trigger firing after insert or delete, for each row, maintaining the number of rows in a separate table. Oracle doesn't need that number so it doesn't maintain it. It does, however, give you all the necessary facilities to maintain that number yorself.
In case you decide to do that, you should be aware that such a trigger is an equivalent of an exclusive lock on the table for the duration of a transaction. Only one transaction, the one holding the lock on the count, will be able to insert at any given count. Also, you will have a ton of expensive transaction restarts when transactions discover that the value they have been waiting for has changed since the transaction has started. In other words, you will turn OLTP into LIETP, transaction processing with speeds like the ones on Long Island Expressway. In case you don't really know what LIE looks like, it suffices to say that it is called "world's largest parking lot".

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