Re: is it possible in pl/sql?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: steve@xxxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 10:31:37 +0000
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
Other related posts:
