Re: is it possible in pl/sql?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 10 Feb 2005 10:31:37 +0000
Message-ID: <7765c89705021002312808dc4a@mail.gmail.com>

On Thu, 10 Feb 2005 09:37:44 +0000, Steve Jelfs <steve_at_trolltec.co.uk> wrote:
> Not sure about count(1) being more efficient. I, personally, have never
> seen any evidence for that!
> Steve

That is because it isn't

USER @ orcl>drop table t1;

Table dropped.

USER @ orcl>create table t1
  2 nologging
  3 as select a.*
  4 from all_objects a,all_objects b
  5 where rownum < 1000001;

Table created.

USER @ orcl>set autotrace on statistics
USER @ orcl>select count(*)
  2 from t1;

  COUNT(*)

---

   1000000

1 row selected.

Statistics

---

         28  recursive calls
          0  db block gets
      10997  consistent gets
      11282  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

USER @ orcl>/

  COUNT(*)

---

   1000000

1 row selected.

Statistics

---

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

USER @ orcl>;
  1 select count(*)
  2* from t1
USER @ orcl>/

  COUNT(*)

---

   1000000

1 row selected.

Statistics

---

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

USER @ orcl>select count(1)
  2 from t1;

  COUNT(1)

---

   1000000

1 row selected.

Statistics

---

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

USER @ orcl>/

  COUNT(1)

---

   1000000

1 row selected.

Statistics

---

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

USER @ orcl>

note that once we have settled down the number of consistent gets and consistent reads is identical for the two alternative plans as is the network traffic.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 10 2005 - 05:34:21 CST