Re: Select count(*) in Oracle and MySQL

From: <fitzjarrell_at_cox.net>
Date: Tue, 09 Oct 2007 08:35:09 -0700
Message-ID: <1191944109.301392.195850@g4g2000hsf.googlegroups.com>

On Oct 9, 9:41 am, "richard.drouill..._at_gmail.com" <richard.drouill..._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-coun...
>
> - Rich

I don't:

SQL> create table test
  2 as select rownum rwnum, object_name   3 from dba_objects;

Table created.

SQL>
SQL> set autotrace on echo on timing on
SQL> spool count_test.lst
SQL>
SQL> select count(*)

  2 from test;

  COUNT(*)

---

     11008

Elapsed: 00:00:00.06

Execution Plan

---

Plan hash value: 3467505462

---

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT   |      |     1 |    13   (8)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 11008 |    13   (8)| 00:00:01 |
-------------------------------------------------------------------

Note

---

• dynamic sampling used for this statement

Statistics

---

          5  recursive calls
          0  db block gets
         91  consistent gets
         42  physical reads
          0  redo size
        226  bytes sent via SQL*Net to client
        245  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select count(rowid)
  2 from test;

COUNT(ROWID)

---

       11008

Elapsed: 00:00:00.06

Execution Plan

---

Plan hash value: 3467505462

---

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    12 |    13   (8)|
00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    12 |
|          |

| 2 | TABLE ACCESS FULL| TEST | 11008 | 129K| 13 (8)| 00:00:01 |

---

Note

---

• dynamic sampling used for this statement

Statistics

---

         27  recursive calls
          0  db block gets
         93  consistent gets
          0  physical reads
          0  redo size
        231  bytes sent via SQL*Net to client
        245  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> Same elapsed time, fewer recursive calls for select count(*). I don't see the 'benefit' of using 'select count(rowid)'.

Oh, and I'm still waiting for the reference from Oracle Corporation to recommend such a change.

David Fitzjarrell Received on Tue Oct 09 2007 - 10:35:09 CDT