Updating non Key-Preserved Table¶

There used to be the BYPASS_UJVC hint that allowed to update a non key-preserved table. It is still there, but it must have stopped working around 11.2. It turns out that Oracle introduced a fix control that seems to be doing the same in 19.10.

Demonstration¶

Here is a short demonstration of this fix control.

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL>
SQL> col fix_control for a90
SQL>
SQL> select xmltype.createxml(cursor(
  2           select *
  3             from v$system_fix_control
  4            where bugno = 19138896)) fix_control
  5    from dual;

FIX_CONTROL
------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <BUGNO>19138896</BUGNO>
    <VALUE>0</VALUE>
    <SQL_FEATURE>QKSFM_DML_19138896</SQL_FEATURE>
    <DESCRIPTION>allow update of join view without key preserved property</DESCRIPTION>
    <EVENT>0</EVENT>
    <IS_DEFAULT>1</IS_DEFAULT>
    <CON_ID>3</CON_ID>
  </ROW>
</ROWSET>


SQL>
SQL> create table t1 (
  2    pk_col int,
  3    t1_col varchar2(30));

Table created.

SQL>
SQL> create table t2 (
  2    pk_col int,
  3    t2_col varchar2(30));

Table created.

SQL>
SQL> insert into t1 values (1, 't1_1');

1 row created.

SQL> insert into t1 values (2, 't1_2');

1 row created.

SQL>
SQL> insert into t2 values (2, 't2_2');

1 row created.

SQL> insert into t2 values (3, 't2_3');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from t1;

    PK_COL T1_COL
---------- ------------------------------
         1 t1_1
         2 t1_2

SQL>
SQL> select * from t2;

    PK_COL T2_COL
---------- ------------------------------
         2 t2_2
         3 t2_3

Let us try to update all rows in T2 with the matching value from T1:

SQL> update (select t2_col, t1_col
  2            from t1, t2
  3           where t2.pk_col = t1.pk_col)
  4     set t2_col = t1_col;
   set t2_col = t1_col
       *
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Cannot do that. BYPASS_UJVC does not work either.

SQL> update --+ bypass_ujvc
  2         (select t2_col, t1_col
  3            from t1, t2
  4           where t2.pk_col = t1.pk_col)
  5     set t2_col = t1_col;
   set t2_col = t1_col
       *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Now with fix control 19138896:

SQL> update --+ opt_param('_fix_control' '19138896:1')
  2         (select t2_col, t1_col
  3            from t1, t2
  4           where t2.pk_col = t1.pk_col)
  5     set t2_col = t1_col;

1 row updated.

SQL>
SQL> select *
  2    from t2;

    PK_COL T2_COL
---------- ------------------------------
         2 t1_2
         3 t2_3

SQL>
SQL> roll
Rollback complete.

If I try to add a duplicate row to T1 making this update non-deterministic, so that there is an attempt to update a row from T2 more than once, I get an error:

SQL> insert into t1 values (2, 't1_22');

1 row created.

SQL> update --+ opt_param('_fix_control' '19138896:1')
  2         (select t2_col, t1_col
  3            from t1, t2
  4           where t2.pk_col = t1.pk_col)
  5     set t2_col = t1_col;
          from t1, t2
               *
ERROR at line 3:
ORA-30926: unable to get a stable set of rows in the source tables

Conclusion¶

Altrough I generally prefer either rewriting such statements to MERGE or adding missing constraints, the fix control 19138896 brings another option to the table. It also has certain advantages, to name a few:

There is no need to rewrite the query. The fix control can be added through an SQL patch, for example.
The fix control can be set at the session or instance levels.