Re: number of rows in a table

From: Bob Withers <Bob.Withers_at_wcom.com>
Date: Mon, 03 May 1999 18:41:30 GMT
Message-ID: <MPG.1197a9fe816dfbac989687@news.mcit.com>

In article <7gkn1v$afc$1_at_nnrp1.dejanews.com>, kal121_at_yahoo.com says...
> COUNT(1) or COUNT(ROWID) are supposedly faster.
>
> COUNT(*) requires internal checks that can cause a slowdown.
>
> I've run some tests that seem to verify that the above two methods can be
> faster on large tables.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

On 12/31/1998 Jeremiah Wilton posted the following which speaks volumes to this subject:

On Wed, 30 Dec 1998, Jurij Modic wrote:
>
> On Tue, 29 Dec 1998 Jonathan Ingram <jonathan.ingram_at_miname.com> wrote:
>
> >boonsong wrote:
> >
> >> Any way to get RowCound by not using Select count(*) from table
> >
> >Yes. Don't select count (*); instead, do one of the following:
> >
> > select count (1) from <table>;
> > select count (rowid) from <table>;
>
> Which, in any case, will return exactly the same result as "select
> count(*)"

Indeed. Lets put this one to rest:

SQL> set autotrace on

SQL> select count (*) from foo;

  COUNT(*)

---

     48880

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=1 Bytes=182504)    1 0 SORT (AGGREGATE)
   2 1 INDEX (FULL SCAN) OF 'PK_FOO' (UNIQUE) (Cost=61 Card=26072 Bytes=182504)

Statistics

---

          0  db block gets
        718  consistent gets
          0  physical reads
        577  bytes sent via SQL*Net to client
        918  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count (1) from foo;

  COUNT(1)

---

     48880

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=1 Bytes=182504)    1 0 SORT (AGGREGATE)
   2 1 INDEX (FULL SCAN) OF 'PK_FOO' (UNIQUE) (Cost=61 Card=26072 Bytes=182504)

Statistics

---

          0  db block gets
        718  consistent gets
          0  physical reads
        580  bytes sent via SQL*Net to client
        918  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count (rowid) from foo;

COUNT(ROWID)

---

       48880

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=1 Bytes=182504)    1 0 SORT (AGGREGATE)
   2 1 INDEX (FULL SCAN) OF 'PK_FOO' (UNIQUE) (Cost=61 Card=26072 Bytes=182504)

Statistics

---

          0  db block gets
        718  consistent gets
          0  physical reads
        584  bytes sent via SQL*Net to client
        922  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah

Received on Mon May 03 1999 - 13:41:30 CDT