I just created the following test: dgs@(protected):/opt/oracle/product/9.2.0>sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Feb 16 22:31:45 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production
dbalert199.DGS.SYS SQL> create table test(c1 number); dbalert199.DGS.SYS SQL> insert into test values (1); dbalert199.DGS.SYS SQL> / dbalert199.DGS.SYS SQL> / dbalert199.DGS.SYS SQL> / dbalert199.DGS.SYS SQL> insert into test values (null); dbalert199.DGS.SYS SQL> / dbalert199.DGS.SYS SQL> / dbalert199.DGS.SYS SQL> / dbalert199.DGS.SYS SQL> select count(c1), count(*), count(1) from test;
To see whether this has changed through history I did the very same test, with the very same results, on the oldest version I have available. :
IOR: Two Process ORACLE V4.1.1 - Production IOR: System global area (SGA) is 15856 (16K) bytes composed of 1100 bytes of fixed data 11684 bytes of variable data, 1024 bytes of before image buffers, and 2048 bytes of buffers. IOR: ORACLE warm started.
=== If you think education is expensive, try ignorance. (Derek Bok) ===
Upcoming appearances:
* Mar 6-10, 2005: Dallas, Hotsos Symposium: Data Guard Performance Issues
On Tue, 2005-02-15 at 18:32, Mark Bole wrote:
> Looney, Jason wrote: > > > My understanding was in the past count(*) returned a count of all non-null > > rows, where count(1) (or any constant) returned a count of all rows. I just > > verified this with 10g and this is not the case. The performance difference > > was to perform a full table scan, instead of counting leaf blocks in a > > primary key index or something like that. So maybe this was true in 6 or 7? > > > > > [...] > > For what it's worth, my recollection is the same, except I think it was > the other way around -- count('x') (based on unique index) would return > only non-nulls, because of the way regular indexes handle nulls, while > count(*) (full table scan) would return all rows. > > I couldn't quickly reproduce the behavior in 10g either, and didn't > bother with an older version.
