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:
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

12 comments:

  1. The same is for other functions with no arguments:

    create or replace function get1 return number is
    begin
    return 1;
    end;
    /

    CREATE OR REPLACE PROCEDURE mu
    AS
    xx mumu.c%TYPE;
    nn number;
    BEGIN
    SELECT c, get1
    INTO xx, nn
    from mumu;
    END;
    /

    We can covered the table by a subquery, after that procedure will be valid after alter:

    CREATE OR REPLACE PROCEDURE mu
    AS
    xx mumu.c%TYPE;
    BEGIN
    SELECT c
    INTO xx
    FROM (select c from mumu)
    WHERE rownum = 1;
    END;
    /

    So, I can assume that no argument function (and some pseudocolumns) is considered as depend on all available fields like *.

    ReplyDelete
  2. To make things even more interesting:

    1.
    If you rewrite MU as follows:
    create or replace procedure mu as
    xx mumu.c%type;
    zz number:=1;
    begin
    select c into xx from mumu where z=zz;
    end;
    it will be invalidated after ALTER TABLE ADD COLUMN.

    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.

    Regards,
    Oleksandr

    ReplyDelete
  3. Moreover, if we add brackets to the ROWNUM (or other function or a pseudocolumn that is represented in standard package as function) in thе procedure MU:

    CREATE OR REPLACE PROCEDURE mu
    AS
    xx mumu.c%TYPE;
    BEGIN
    SELECT c
    INTO xx
    from mumu
    where rownum() = 1;
    END;
    /

    Oracle will not invalidate MU after adding a column.

    ReplyDelete
  4. al0,

    To avoid invalidation in first case, it is possible to qualify variable with the name of the procedure:

    create or replace procedure mu as
    xx mumu.c%type;
    zz number:=1;
    begin
    select c into xx from mumu where z=mu.zz;
    end;
    /

    ReplyDelete
  5. 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.

    ReplyDelete
  6. I recall considering the difficulties in column level dependencies a few years back.

    http://blog.sydoracle.com/2007/01/column-level-dependencies-in-11g.html

    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.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Just a thought regarding the quiz - don't forget to read/check the database minimum version for each quiz, people - I got burned on that question thinking on 10g ;-)

    ReplyDelete
  9. I hardly see how qualifying of built-in functions may improve readability.

    ReplyDelete
  10. There are a some examples where the invalidation doesn't happen when it is expected


    /******************************************************/
    Example 1. No invalidation when using of hidden columns:
    /******************************************************/

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set serveroutput on
    SQL> drop table mumu;
    Table dropped.
    SQL> create table mumu(x number, c varchar2(1));
    Table created.
    SQL> create index iuiu on mumu(1);
    Index created.
    SQL> insert into mumu values (1, 'A');
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> variable col_name varchar2(30)
    SQL> column column_name new_value col_name noprint
    SQL> select c.column_name from user_tab_cols c where c.TABLE_NAME = 'MUMU' and c.COLUMN_NAME not in ('X','C');

    SQL> create or replace procedure MU as
    2 xx mumu.c%type;
    3 begin
    4 select c
    5 into xx
    6 from mumu
    7 where x = &col_name;
    8 dbms_output.put_line(xx);
    9 end;
    10 /
    old 7: where x = &col_name;
    new 7: where x = SYS_NC00003$;

    Procedure created.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> exec MU;
    A

    PL/SQL procedure successfully completed.

    SQL> drop index iuiu;
    Index dropped.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> exec MU;
    BEGIN MU; END;

    *
    ERROR at line 1:
    ORA-00904: "SYS_NC00003$": invalid identifier
    ORA-06512: at "TEST_USER.MU", line 4
    ORA-06512: at line 1

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> alter procedure mu compile;

    Warning: Procedure altered with compilation errors.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    INVALID



    /***************************************************************/
    Example 2. No invalidation when ambiguity in qualifier resolving:
    /***************************************************************/

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set serveroutput on
    SQL> drop table mumu;
    Table dropped.
    SQL> create table mumu(x number);
    Table created.
    SQL> insert into mumu values (1);
    1 row created.
    SQL> insert into mumu values (2);
    1 row created.
    SQL> commit;
    Commit complete.

    SQL> create or replace procedure mu as
    2 x number;
    3 z number := 1;
    4 begin
    5 select mu.x
    6 into mu.x
    7 from mumu mu
    8 where mu.x = mu.z;
    9 dbms_output.put_line(mu.x);
    10 end;
    11 /

    Procedure created.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> exec MU;
    1

    PL/SQL procedure successfully completed.

    SQL> alter table mumu add z number default 2;
    Table altered.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> exec MU;
    1

    PL/SQL procedure successfully completed.

    SQL> alter procedure mu compile;
    Procedure altered.

    SQL> exec MU;
    2

    PL/SQL procedure successfully completed.

    ReplyDelete
  11. /***************************************************************/
    Example 3. No invalidation when ambiguity in qualifier resolving:
    /***************************************************************/

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set serveroutput on
    SQL> drop table mumu;
    Table dropped.
    SQL> create table mumu(x number);
    Table created.
    SQL> insert into mumu values (1);
    1 row created.
    SQL> insert into mumu values (2);
    1 row created.
    SQL> commit;
    Commit complete.

    SQL> create or replace procedure mu as
    2 type rec_t is record (x number, z number);
    3 mumu rec_t;
    4 begin
    5 mumu.z := 1;
    6 select mumu.x
    7 into mumu.x
    8 from mumu
    9 where mumu.x = mumu.z;
    10 dbms_output.put_line(mumu.x);
    11 end;
    12 /

    Procedure created.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> exec MU;
    1

    PL/SQL procedure successfully completed.

    SQL> alter table mumu add z number default 2;
    Table altered.

    SQL> select status from user_objects where object_name = 'MU';

    STATUS
    -------
    VALID

    SQL> exec MU;
    1

    PL/SQL procedure successfully completed.

    SQL> alter procedure mu compile;
    Procedure altered.

    SQL> exec MU;
    2

    PL/SQL procedure successfully completed.

    /***********************************/
    Example 4. No invalidation when join:
    /***********************************/

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set serveroutput on
    SQL> drop table mumu;
    Table dropped.
    SQL> drop table kuku;
    Table dropped.
    SQL> create table mumu (id number, val1 varchar2(4));
    Table created.
    SQL> create table kuku (id number, val2 varchar2(4));
    Table created.
    SQL> insert into mumu values (1, 'mumu');
    1 row created.
    SQL> insert into kuku values (1, 'kuku');
    1 row created.
    SQL> commit;
    Commit complete.

    SQL> create or replace procedure mumu_natural_join_kuku is
    2 l_msg varchar2(5) := 'kuku!';
    3 id number := 1;
    4 begin
    5 select 'mumu!'
    6 into l_msg
    7 from mumu natural join kuku;
    8 dbms_output.put_line(l_msg);
    9 exception
    10 when no_data_found then
    11 dbms_output.put_line(l_msg);
    12 end;
    13 /

    Procedure created.

    SQL> select o.status from user_objects o where o.object_name = 'MUMU_NATURAL_JOIN_KUKU';

    STATUS
    -------
    VALID

    SQL> exec mumu_natural_join_kuku;
    mumu!

    PL/SQL procedure successfully completed.

    SQL> alter table kuku add val1 varchar2(4) default 'kuku';
    Table altered.

    SQL> select o.status from user_objects o where o.object_name = 'MUMU_NATURAL_JOIN_KUKU';

    STATUS
    -------
    VALID

    SQL> exec mumu_natural_join_kuku;
    kuku!

    PL/SQL procedure successfully completed.

    ReplyDelete
  12. 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.

    I'd feel safer still if locally scoped items (ie PL/SQL variables) took precedence over global (schema) items though.

    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.

    ReplyDelete