Re: Any way to get RowCount by not using Select count(*) from table

From: Jeremiah Wilton <jeremiah_at_wolfenet.com>
Date: Thu, 31 Dec 1998 09:09:19 -0800
Message-ID: <Pine.OSF.4.02.9812310840300.31474-100000@gonzo.wolfenet.com>

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 Thu Dec 31 1998 - 11:09:19 CST