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:

SQL: CREATE TABLE mumu
  2  (
  3     x   NUMBER
  4   , c   VARCHAR2 (5 BYTE)
  5   , z   NUMBER
  6  );

Table created.

SQL: INSERT INTO mumu (x, c)
  2       VALUES (2, 'x');

1 row created.

SQL: INSERT INTO mumu (x, c)
  2       VALUES (3, 'c');

1 row created.

SQL: COMMIT;

Commit complete.

SQL: CREATE OR REPLACE PROCEDURE mu
  2  AS
  3     xx   mumu.c%TYPE;
  4  BEGIN
  5     SELECT c
  6       INTO xx
  7       FROM mumu
  8      WHERE ROWNUM = 1;
  9  END;
 10  /

Procedure created.

SQL: SELECT status
  2    FROM user_objects
  3   WHERE object_name = 'MU';

STATUS
-------
VALID

SQL: ALTER TABLE mumu ADD y NUMBER;

Table altered.

SQL: SELECT status
  2    FROM user_objects
  3   WHERE object_name = 'MU';

STATUS
-------
INVALID

If I remove the reference to ROWNUM (change to "z = 1" for example), then the program unit is not invalidated.

I do not know why this happening. Does anyone "out there" have any ideas or any other comments about this quiz?

Cheers, SF