Re: Select count(*) in Oracle and MySQL

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".
>

For an industrial strength DBMS it is illogical to attempt to track such a number for two reasons:
1. No application ever does such a query.

    COUNT(*) is typically done in conjunction with a GROUP BY 2. Maintaining a centralized counter immediately becomes a "hot latch".

    If mySQL were to attempt any decent size OLTP benchmark that     would be the first "feature" to go...

So, yes, the behavior is normal and it is intended. The solution for you is to create an index. You will find the same behavior in DB2, IDS and very likely SQL Server. If I had to take a guess I'd also include Postgress in that list...

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab