Re: Select count(*) in Oracle and MySQL

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

You can keep track of the number of rows by using statistics, but Oracle won't use this number as it may not be accurate. Statistics aren't updated in real time.
If you don't have an index Oracle will conduct a full table scan up to the High Water Mark of the table, even if the table is empty. If you do have an index Oracle will conduct an index_fast_full_scan.

I would consider the MySQL strategy cheating and potentially dangerous and/or limiting scalability (You can't allow any readers while this statistic is updated). In Oracle readers don't block writers. Why do you need the count(*) anyway?
Not to test for existence of a record hopefully?

-- 
Sybrand Bakker
Senior Oracle DBA