Thanks
Jay Fowler, February 02, 2009 - 5:16 pm UTC
Just to be clear, any time the DDL of a table changes (specifically the column name changes) all views that are dependant on that table will have to be re-created with the "create or replace" syntax rather than just recompiling. True?
Thanks again.
February 03, 2009 - 9:17 am UTC
Just to be clear:
NO
In general the answer is absolutely *NOT*. Add a column to a table - does a view that was created in support of an application interface to the database need to change? Probably not - not unless the application code is going to change too - which means you are recreating the entire stack. But in general *NO*, you would not.
Rename a column? If your view accesses the column by the old name, it will obviously have to be refactored, rewritten, you are making a change to something that depends on it - just like your code that uses the view will have to be modified.
q: What are views used for?
a: they are typically used to hide from the application changes that take place to the underlying physical structures.
If you change a name of a column and the view BY YOUR REQUIREMENT needs to be modified - then so be it (just like any application that queries the view must now be modified and so on and so on)
Think of views like code (plsq - do - really - they are). A SQL Query is a subroutine, a method. If you have requirements that change (underlying physical schema is modified in a way that dramatically affects your code) your code, then change the code.
A view is SQL, SQL is code, code is code.
Michael Virnstein, February 03, 2009 - 2:40 am UTC
Just to be clear, any time the DDL of a table changes (specifically the column name changes) all
views that are dependant on that table will have to be re-created with the "create or replace"
syntax rather than just recompiling. True?
No, only the views that reference one of the changed columns:
SQL> create table t_viewtest (x number, y number);
Table created
SQL> create or replace view v_viewtest_x as select x from t_viewtest;
View created
SQL> create or replace view v_viewtest_y as select y from t_viewtest;
View created
SQL> alter table t_viewtest rename column y to z;
Table altered
SQL> select * from v_viewtest_x;
X
----------
SQL> select * from v_viewtest_y;
select * from v_viewtest_y
ORA-04063: view "TBO.V_VIEWTEST_Y" has errors
"The "*" has always been evaluated at compile time ... "
Sokrates, February 03, 2009 - 1:13 pm UTC
there's still an issue with it in 10.2, where your statement is not totally true:
sokrates@10.2 > create table jay_test_views
2 as
3 select 'xxxxxxxx' as col1, 'yyyyyyyyyy' as col2 from dual;
Table created.
sokrates@10.2 > create view expanded as
2 select * from jay_test_views;
View created.
sokrates@10.2 >
sokrates@10.2 > alter table jay_test_views add constraint i_assume_this_is_the_pk primary key ( col1 );
Table altered.
sokrates@10.2 >
sokrates@10.2 > create view not_expanded as
2 select * from jay_test_views natural join (select col1 from jay_test_views);
View created.
sokrates@10.2 > select view_name, text from user_views where view_name in ('EXPANDED', 'NOT_EXPANDED');
VIEW_NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
EXPANDED
select "COL1","COL2" from jay_test_views
NOT_EXPANDED
select * from jay_test_views natural join (select col1 from jay_test_views)
sokrates@10.2 > alter table jay_test_views rename column col2 to col3 ;
Table altered.
sokrates@10.2 > select * from expanded;
select * from expanded
*
ERROR at line 1:
ORA-04063: view "DP.EXPANDED" has errors
sokrates@10.2 > select * from not_expanded;
COL1 COL2
-------- ----------
xxxxxxxx yyyyyyyyyy
sokrates@10.2 > desc not_expanded
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL CHAR(8)
COL2 CHAR(10)
sokrates@10.2 > REM
now it is getting really funny ...sokrates@10.2 > select col2 from not_expanded;
COL2
--------
xxxxxxxx
sokrates@10.2 > select col2, col3 from not_expanded;
COL2 COL3
-------- ----------
xxxxxxxx yyyyyyyyyy
sokrates@10.2 > select col2, col3, col1 from not_expanded;
select col2, col3, col1 from not_expanded
*
ERROR at line 1:
ORA-00904: "COL3": invalid identifier
February 03, 2009 - 2:00 pm UTC
9.2.0.8 - reproduced
10.2.0.4 - reproduced
11.1.0.7 - not reproduced
now, to see if there was a bug filed...
bug filed
Sokrates, February 03, 2009 - 4:13 pm UTC
February 03, 2009 - 5:18 pm UTC
see, I've got so many bits of trivia stuck in my head, I often forget what I've already seen