Re: Select count(*) in Oracle and MySQL

On Wed, 19 Sep 2007 12:54:24 -0700, Occidental
<Occidental@xxxxxxxxxxx> wrote:

On Sep 19, 3:24 pm, sybra...@xxxxxxxxx wrote:
On Wed, 19 Sep 2007 11:40:21 -0700, Occidental

<Occiden...@xxxxxxxxxxx> 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".

Databases Management Systems are about *sets*.
Sets aren't flat files.
That said it seems rather absurd you want to know at any given time
how many rows there are in a table.
The question is irrelevant as you aren't filtering your data at the
cllient, are you.
That said, you either don't know how to use a DBMS like Oracle (given
the nature of your comment) or Davids remark (given your comment) is
correct: As you respond with DBMS bashing, you must be a MySQL
aficionado, who is up for a flamewar, because most MySQL aficionados
are very intolerant, indeed arrogant, towards other DBMSes, especially
Oracle.
I suggest you come back over a few decades when you MySQL aficionados
have implemented the next 2 percent of Oracle functionality.

For now: please get lost!


--
Sybrand Bakker
Senior Oracle DBA
.

Relevant Pages

  • Re: SQL parsing
    ... premature optimization - even for an Oracle DBMS. ... but even a complete parser won't cope with stored procedures. ... the local query cache. ...
    (comp.lang.php)
  • Re: OT: How I learned to stop worrying and love the RDBMS
    ... not used either to replace TurboIMAGE for a migration client. ... Oracle and DB2 to replace TurboIMAGE for migration clients and our partners ... MySQL has become. ... The primary reason for our Oracle, DB2 and MS SQL Server emphasis is because ...
    (comp.sys.hp.mpe)
  • Re: MySQLdb slow on MySQL 5
    ... Oracle bought a small Finnish ... company which developed the transaction-aware table- ... backend for MySQL. ... It's one thing if Oracle fails to comply to all standards, ...
    (comp.lang.python)
  • Re: Oracle vs MySql Performance
    ... Performance on MySql was adequate but as our primary DB platform is ... Oracle we influenced the 3rd part to introduce an Oracle version (NB: ... To facilitate this process the vendor has used almost pure ANSI Sql so ... Look at the wait events in the raw trace file for clues ...
    (comp.databases.oracle.server)
  • Re: Select count(*) in Oracle and MySQL
    ... The same query in Oracle takes about 6 minutes, ... The MySQL table has no primary key either. ... You can compare bananas to prunes, ...
    (comp.databases.oracle.server)