Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Select count(*) in Oracle and MySQL

Re: Select count(*) in Oracle and MySQL

From: <xhoster_at_gmail.com>
Date: 20 Sep 2007 17:09:11 GMT
Message-ID: <20070920130913.197$iT@newsreader.com>


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.

MySQL serializes inserts into its tables, so there is no meaningful additional overhead to keeping a count of the number of rows. So it does it. Oracle does not serialize inserts, as doing so would be massive concurrency bottleneck, and so has no way to keep an accurate row-count. If you use the more concurrency-friendly engines that newer MySQLs support, you will find they also need to count rows in order to get a row count.

> Any comments?

Well, sinse you asked. I suspect you are either a dolt or a troll.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
Received on Thu Sep 20 2007 - 12:09:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US