Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select count(*) in Oracle and MySQL
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