It seems Oracle 11g introduces a difference between count(*) and count(1). The way this happens is just the opposite of what I was thinking would happen. NB: I ran my test using “11.1.0.6 32bits” on Ubuntu Linux 7.04 (Feisty) which is not officially supported1, and which has already lead me to some unexpected behaviors. If this difference with count() is really the 11g way and not buggy behavior related to the Ubuntu install, I’m glad to have found it.
Here’s what you can do to observe (or confirm or dispute) this.
1. Reproduce the test
I’ll need the simplest table possible:
create table gg( col1 number);
I’ll create two distinct views of this table as below :
gg_v1makes use of acount(1)gg_v2makes use of acount(*)
create view gg_v1 as select count(1) num from gg; create view gg_v2 as select count(*) num from gg;
I’ll let you check that these two views are valid. And then I’ll let you add a column:
alter table gg add (col2 number);
2. Display the result
The query below displays the status of the two views after the change on the table:
col object_name format a10
col object_type format a5
col status format a8
select object_name, object_type, status
from user_objects
where (object_name='GG_V1'
or object_name='GG_V2')
and object_type = 'VIEW'
order by object_name, object_type;
OBJECT_NAM OBJEC STATUS
---------- ----- --------
GG_V1 VIEW INVALID
GG_V2 VIEW VALID
So the view with count(1) has been invalidated, while the other with count(*) has not!
I’ve read this section of the 11g Concept Guide that explains the dependencies when you add a column to a table, but I have absolutely no idea why the count(1) view is invalidated. I’ll be very interested if one of you knows.
1. For reference, see also Augusto’s item, Installing Oracle 11g on Ubuntu Linux 7.04.
5 Comments. Leave new
Just for completeness: could you check what happens to select count(null) ? We have had a disussion in a newsgroup about count(1) vs. count(null).
I just verified this is indeed different from behaviour in 10.2.0.3 on Solaris 10:
(9.2.0.8 as well)
SQL*Plus: Release 10.2.0.3.0 – Production on Thu Oct 4 17:38:11 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> — create simple table
SQL>
SQL> create table gg(
2 col1 number);
Table created.
SQL>
SQL> — create two distinct views of this table as below :
SQL> — gg_v1 makes use of a count(1)
SQL> — gg_v2 makes use of a count(*)
SQL>
SQL> create view gg_v1 as
2 select count(1) num from gg;
View created.
SQL>
SQL> create view gg_v2 as
2 select count(*) num from gg;
View created.
SQL>
SQL> — check that these two views are valid.
SQL>
SQL> col object_name format a10
SQL> col object_type format a5
SQL> col status format a8
SQL>
SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name=’GG_V1′
4 or object_name=’GG_V2′)
5 and object_type = ‘VIEW’
6 order by object_name, object_type;
OBJECT_NAM OBJEC STATUS
———- —– ——–
GG_V1 VIEW VALID
GG_V2 VIEW VALID
SQL>
SQL> — And then add a column:
SQL>
SQL> alter table gg add (col2 number);
Table altered.
SQL>
SQL> — 2. Display the result
SQL>
SQL> — The query below displays the status of the two views after the change on the table:
SQL>
SQL> col object_name format a10
SQL> col object_type format a5
SQL> col status format a8
SQL>
SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name=’GG_V1′
4 or object_name=’GG_V2′)
5 and object_type = ‘VIEW’
6 order by object_name, object_type;
OBJECT_NAM OBJEC STATUS
———- —– ——–
GG_V1 VIEW INVALID
GG_V2 VIEW INVALID
SQL>
SQL>
SQL> drop table gg;
Table dropped.
SQL>
SQL> drop view gg_v1;
View dropped.
SQL>
SQL> drop view gg_v2;
View dropped.
SQL>
Sorry for that count(null), different discussion, should return 0 at any time… was a discussion about select null from dual.
[…] Grégory Guillou discovered an unexpected difference between count(*) and count(1). Also here at home, Robert Hamel gives a bad rating to 11g’s new pivot table feature. […]
Hi Greg,
Check about improvement concerning 11G, this is the key. I’ve found that behavior is call “Fine grained dependency tracking”.
While certain things require view to be invalidated, somes do not. For example with DDL “ADD COLUMN” :
count(*), select *, select col1 (must exist before the add). And so on.
Check this one https://laurentschneider.com/wordpress/2006/10/11g-new-features.html
I quote Laurent Schneider :
”
Fine grained dependency tracking. Currently, if you have a view or a function based on a table, and if you modify that table, the view is getting invalid. This is what Bryn called : aggressive object invalidation. Now in 11g it has improved, so if you modify an element of the table which is not included in that view (ex: you add a column), the view is not invalidated. This is wise.
”
Regards,
Virgile CREVON