Concepts Guide: 6/27 - Dependencies Among Schema Objects

Almost nice to have a short chapter. I cheated a peeked ahead and saw that Chapter 7 is only 6 pages; how the heck did they squeeze the Data Dictionary into 6 pages? Guess we will have to wait and see.

I like how Oracle automatically attempts to recompile dependent objects if the referenced object is changed - the discussion in this chapter highlights Oracle's infrastruture (although does not detail it) and how it all happens like black magic. =) Here is a quick example:

SQL > create table t1 (a char(1));

Table created.

SQL > create view v1 as select * from t1;

View created.

SQL > create view v2 as select * from v1;

View created.

SQL > select object_name, status from dba_objects where object_name like '__';

OBJECT_NAME STATUS

------------------------------ -------

V1 VALID

V2 VALID

T1 VALID

SQL > alter table t1 add (b char(1));

Table altered.

SQL > select object_name, status from dba_objects where object_name like '__';

OBJECT_NAME STATUS

------------------------------ -------

V1 INVALID

V2 INVALID

T1 VALID

SQL > select * from v2;

no rows selected

SQL > select object_name, status from dba_objects where object_name like '__';

OBJECT_NAME STATUS

------------------------------ -------

V1 VALID

V2 VALID

T1 VALID

Note how V2 depends on V1, which depends on T1. Even though the text is not clear on whether or not a recurssive recompilation will occur, we can see that in practice, it does. Cool stuff.

The notes on page 6 (Object Name resolution) are key to remember. It seems easy sometimes to forget the order of precedence:

1. own schema
2. public synonym
3. schema matches fully-qualified object owner

There is an important ramification to this:

Because of how Oracle resolves references, it is possible for an object to depend on the nonexistence of other objects. This situation occurs when the dependent object uses a reference that would be interpreted differently were another object present.

For instance, it is possible that V1 needs to have T1 not be in the current schema, but rather a synonym.

Right below that is a section on how SQL also maintains a dependency on the referenced object. Because Oracle (as of Oracle 11gR1) does not fully support surgically removing individual sql cursors, one workaround is to change the dependent object, which immediately invalidates any associated cursors. Great for if you want a new query plan because something changed and you do not want to redo the stats. Well... kinda great; not to many shops allow the DBA to make changes to the table on the fly in Production. =)

The section on remote dependencies was slightly interesting - I did not realize that Oracle checked not only on timestamps, but also on signatures. When you think about it, it is amazing how much Oracle does behind the scenes.