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, 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".
Received on Wed Sep 19 2007 - 14:54:24 CDT
