| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: is it possible in pl/sql?
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?
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Feb 15 10:12:37 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
system_at_LOCALDB> drop table test;
Table dropped.
system_at_LOCALDB> CREATE TABLE test (t1 VARCHAR2(25) NULL, t2 NUMBER NULL);
Table created.
system_at_LOCALDB>
system_at_LOCALDB> INSERT INTO test values (NULL, NULL)
2 /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> /
1 row created.
system_at_LOCALDB> select count(*) from test;
COUNT(*)
9
system_at_LOCALDB> select count(1) from test;
COUNT(1)
9
system_at_LOCALDB> select count(t1) from test;
COUNT(T1)
0
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Lex de Haan
Sent: Tuesday, February 15, 2005 6:01 AM
To: thomas.mercadante_at_labor.state.ny.us; joelgarry_at_anabolicinc.com;
oracle-l_at_freelists.org
Subject: RE: is it possible in pl/sql?
no, certainly not. count(*) and count(<any constant>) should return the same
result, under all circumstances.there used to be a performance difference,
in
the past.
kind regards,
Lex.
Does anyone know what the difference between count(*) & count(1) was *supposed* to be? Is there a theoretical functional difference?
-----Original Message-----
From: Joel Garry [mailto:joelgarry_at_anabolicinc.com]
Sent: Monday, February 14, 2005 7:35 PM
To: oracle-l_at_freelists.org
Subject: Re: is it possible in pl/sql?
Anthony Wilson wrote:
>it's a myth according to the venerable Tom Kyte. The SQL engine
silently rewrites >count(1) to count(*):
Funny, he mentions that is a fact, but I've missed the reference to where
the
fact is shown. Though I'm inclined to believe anything Tom says, I find
this
particular one ironic in that he is talking about facts and opinions when he
says it. And I'm certainly glad he mentions the differing case of 7.x, as
that
means I didn't get the myth completely out of thin air or confused it with
some
other function.
Just means that old habits die hard.
Joel Garry
http://www.garry.to
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 15 2005 - 12:20:40 CST
 |
 |