Re: is it possible in pl/sql?
- From: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>
- To: makbo@xxxxxxxxxxx
- Date: Wed, 16 Feb 2005 23:06:46 +0100
I just created the following test:
dgs@dbalert199:/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;
COUNT(C1) COUNT(*) COUNT(1)
========= ======== ========
4 8 8
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.
ORACLE Utilities, Copyright (c) 1979, 1980, 1981, 1982, RSI
UFI version 3.5 - on Wed Feb 16 11:55:54 2005
Connecting to: Two Process ORACLE V4.1.1 - Production
Cannot read command file "login.ufi"
UFI>
Table dropped.
Table created.
1 record created.
1 record created.
1 record created.
1 record created.
1 record created.
1 record created.
1 record created.
1 record created.
COUNT(C1) COUNT(*) COUNT(1)
---------- ---------- ----------
4 8 8
Logged off from ORACLE.
Best regards,
Carel-Jan Engel
===
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
