On Thu, 10 Feb 2005 09:37:44 +0000, Steve Jelfs <steve@xxxxxxxxxxxxxx> 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 -- //www.freelists.org/webpage/oracle-l