Schema-object dependencies : Do Guidelines mentioned in doc for Reducing Invalidation really make sense?

Question

The doc says reference each table through a view so that Modify or delete columns not referenced by the view without invalidating dependent objects. I dont find a single case in which this could actually make sense.

If a dependent object is referring to particular column in table, modify or deleting other columns really does not change the status of dependent object either indirectly through a view or directly.

Answer 1

If a dependent object is referring to particular column in table, modify or deleting other columns really does not change the status of dependent object ...

True, but what if you aren't referring to the columns explicitly?

Using the set-up from your previous question, here's an example where it could make sense:

create table test (num1 number, num2 number);

create or replace procedure refvirew_intab is
begin
  for r in (select * from test) loop
    dbms_output.put_line(r.num1);
  end loop;
end;
/

At this point all three objects are valid. If I drop the unused num2 column:

alter table test drop column num2;

then the procedure is invalidated:

OBJECT_NAME          OBJECT_TYPE         STATUS 
-------------------- ------------------- -------
REFVIREW_INTAB       PROCEDURE           INVALID
TEST                 TABLE               VALID  
VEXTEST              VIEW                VALID  

If the column is re-added and the procedure is created against the view instead:

create or replace view vextest as (select num1 from test);

create or replace procedure refvirew_intab is
begin
  for r in (select * from vextest) loop
    dbms_output.put_line(r.num1);
  end loop;
end;
/

then modifying the table now has no effect:

alter table test drop column num2;

Table TEST altered.

select object_name, object_type, status
from user_objects
where object_name in ('TEST' ,'VEXTEST', 'REFVIREW_INTAB')
order by object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS 
-------------------- ------------------- -------
REFVIREW_INTAB       PROCEDURE           VALID  
TEST                 TABLE               VALID  
VEXTEST              VIEW                VALID  

Now, as you seem to already know, this only works with select *. If the table version has select num1 then dropping the second column has no effect on the procedure, as you suggested in the question. And using select * is frowned upon. In this situation, the procedure would be recompiled automatically, and successfully as long as there really are no references to the dropped column.

Another scenario is if, as in your previous example, you're inserting into a table without specifying the column names (also frowned upon). In that case changing the table definition would invalidate the procedure, possibly rather more catastrophically since it wouldn't recompile successfully if you end up with different number/order/types of columns. If you were inserting into the view via an instead of trigger then the procedure wouldn't care. (The trigger would, and that then might not recompile, depending on what it was doing). That's more related to the first bullet in the docs, not the second one you're asking, though; but does apply to dropping columns too.

So it's arguable whether this is really helpful advice. I'm sure there are people who use select * or inserting without specifying the column names, and some who use views to avoid those occasional side effects. I'm not sure I can see a scenario where a layer of views just to avoid the possibility of dependent object invalidation would be worth the extra management, but some people probably do.

If you're seeing objects being invalidated, even temporarily, you're probably doing something wrong - schema changes should normally be done in a planned and controlled way so side effects are handled properly, including coordinated updates to dependent objects. But again others will have different opinions and priorities.

Answer 2

The documentation guidelines to do everything through views are ridiculous and should be ignored.

I've never seen anyone follow those guidelines. Not even Oracle. The Oracle documentation is usually great but every manual contains some mistakes and weird opinions.

A good program minimizes the levels of indirection and the number of objects. An extra layer on top of everything requires one hell of a good reason. Avoiding a few weird cases, that are only caused by bad programming practices anyway, is not enough to justify the extra complexity of views.