It seems Oracle 11g introduces a difference between
count(1). The way this happens is just the opposite of what I was thinking would happen. NB: I ran my test using “220.127.116.11 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 a
gg_v2makes use of a
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.
Interested in working with Grégory? Schedule a tech call.