Re: Select count(*) in Oracle and MySQL

On Sep 19, 2:54 pm, Occidental <Occiden..._at_comcast.net> wrote:
> On Sep 19, 3:24 pm, sybra..._at_hccnet.nl wrote:
>
>
>
>
>
> > On Wed, 19 Sep 2007 11:40:21 -0700, Occidental
>
> > <Occiden..._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
>
> 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,

My apologies, your post simply read that way in my mind.

> I am simply interested in determining whether
> the long runtime of the Oracle query is normal or reflects some error
> in the installation.

No error in the installation, I expect. The difference is in the engine used, and as stated in another response the MyISAM engine is, well, not a transactional implementation. 250 million rows in an unindexed table requires a table scan to produce a count; that MySQL, in its MyISAM implementation, takes liberties to 'optimize' that leads me to believe the engine may not be as reliable as Oracle. It certainly isn't as robust.

> If MySQL can do it, why not the Oracle?

Because Oracle isn't using MyISAM, Oracle is transactional and Oracle is a far more reliable product not 'optimized' for a lone aggregate query.

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

Why should it without statistics? Tables are dynamic entities, for the most part, and transactional activity changes this record count with regularity. Oracle spends its time providing read consistency and a configuration where readers don't block writers and writers don't block readers. MySQL cannot say the same. I'd rather have read consistency over an 'optimized' select count(*) query any day.

David Fitzjarrell Received on Wed Sep 19 2007 - 16:09:48 CDT