Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Select count(*) in Oracle and MySQL

Re: Select count(*) in Oracle and MySQL

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 10 Oct 2007 09:55:51 +0200
Message-ID: <470c859a$0$240$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1191949360.256259_at_bubbleator.drizzle.com...
> richard.drouillard_at_gmail.com wrote:
>> On Sep 19, 2:40 pm, 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?
>>
>> Oracle recommends using count(rowid) instead of count(*) for
>> performance reasons, I think you'll find this much faster.
>>
>> http://www.thewellroundedgeek.com/2007/09/most-people-use-oracle-count-function.html
>>
>> - Rich
>
> Oracle doesn't recommend it ... you do. And in doing so you are incorrect.
>
> You should remove the recommendation from the website.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

In 11g, there seem to be differences in performance with count(*) and count(1), but only in rare cases like after adding a column to a view, IIRC. Have seen some article about that, I'll try to look it up (but maybe this WAS the quoted article). Count(rowid) is a bad idea for views BTW.

Shakespeare Received on Wed Oct 10 2007 - 02:55:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US