The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

07 January 2011

Exploring nuances of 11g fine-grained dependencies (1841)

The 5 January quiz tested your knowledge or (or, more likely, introduced you to) the Oracle11g fine-grained dependency feature, which maintains dependency information down to columns within tables and parameters within subprograms.

The quiz demonstrates that if I add a column to a query that is executed in a stored program unit, then that the program unit is not invalidated, since that unit couldn't possibly have depended on the new, previously non-existent, column.

One player, Oleksandr, wrote with the following oddity: if your query includes reference to ROWNUM, then Oracle will invalidate the program unit after adding a column. You can see this fact below:

To be honest, this behavior is documented "Dependent object references table in query that references a PL/SQL variable." (for 11.1 it is in Concepts and for 11.2 in Advanced Application Development).

2. Not only ROWNUM causes an invalidation after ALTER TABLE ADD COLUMN - SYSDATE as well: select c into xx from mumu where sysdate=DATE'2010-11-11';or even select to_char(sysdate,'yy') into xx from mumu;The same is true for USER select user into xx from mumu;

Probably, some other functions.So this feature seems to be undocumented.

The use of pseudocolumns and unqualified references to functions and variables in SQL statements create implicit dependencies on all column names of the underlying tables. The reason is that the resolution of these identifiers may be affected by adding, dropping, or renaming columns. The effect of qualified names on fine-grained dependency is briefly discussed on pages 726-727 in Oracle PL/SQL Programming, Fifth Edition (Feuerstein, Steven and Pribyl, Bill, 2009).

Qualifying all identifiers within SQL statements was a recommended practice even before the introduction of fine-grained dependency in Oracle 11g. Following this practice improves readability and insulates one’s code from the effects of adding columns. It also avoids potentially inconsistent results caused by ambiguous column references. In Oracle 10g it is possible to construct a query with ambiguous column references that will successfully compile; however, the values returned by the ambiguous references might change depending on the execution plan developed by the optimizer. Qualifying the references removes the ambiguity and produces consistent results. Oracle 11g appears to catch ambiguous column references better than 10g.

To be honest, most SQLs I use in PL/SQL have either a join or a PL/SQL variable and it isn't something I've thought worth coding around. Nikotin's use of full qualification is interesting, but I'd have to see what happened if I created an MU schema with a ZZ function (and suitable grant) to see if I trust the process.

Nikotin's first three examples all raise issues about this automatic invalidation. The second and third are the 'scariest' as the behaviour of the procedure will change at some arbitrary future period without indication. I feel much safer with the old behaviour.

Example 4 isn't an error. It works the same in 10g except it omits the invalidation/recompilation (which is the purpose of the 11g change). It is a demonstration of why you should never use a NATURAL JOIN though.