On Thu, 10 Feb 2005 09:37:44 +0000, Steve Jelfs <steve@(protected)> 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.
