Thursday, April 8, 2010

Count with View in Oracle 9i vs 11g



Oracle 9i Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
as select count(1) count from t;
View created.

SQL> create view v2
as select count(*) count from t;
View created.

SQL> select object_name, object_type, status
from user_objects
where (object_name in ('V1','V2'))
and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------- --------
V2 VIEW VALID
V1 VIEW VALID

Add another column in the table.
SQL> alter table t add ( c2 number);
Table altered.

SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name in ('V1','V2'))
4 and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------ -------------
V2 VIEW INVALID
V1 VIEW INVALID

Both the views are Invalid in Oracle 9i environment.
Oracle 11g Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
2 as
3 select count(1) count from t;
View created.

SQL> create view v2
2 as
3 select count(*) count from t;
View created.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- -------------- -------------
V1 VIEW VALID
V2 VIEW VALID

Add another column in the table.

SQL> alter table t add(c2 number);
Table altered.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
------------ --------------- -------------
V1 VIEW INVALID
V2 VIEW VALID


The V1 became Invalid. but V2 is valid

0 comments:

Post a Comment