Re: Select count(*) in Oracle and MySQL

On Wed, 19 Sep 2007 12:54:24 -0700, Occidental <Occidental_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, 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