BYE-BYE, BYPASS_UJVC!

It was that hint /*+ BYPASS_UJVC */ that ten years ago already taught me a lesson on how to deal with undocumented hints in productive environments. Admittedly, I still have used it since then for ad hoc data manipulation a few times. Because of a strange behavior in 11gR2 database, I decided to look again at the issue of “one-pass” updates of a join and their alternative using merge sql statement.

What is it exactly?

To understand what the hint does, we introduce a simple example. We have two tables T1 and T2, and want to update a numeric column num_col in T1 with the corresponding data from table T2. The join between these two tables is made using columns T1_PK and T2_PK:

 
CREATE TABLE t1 (t1_pk NUMBER, num_col NUMBER);

INSERT INTO t1 (t1_pk, num_col) VALUES (1,NULL);
INSERT INTO t1 (t1_pk, num_col) VALUES (2,NULL);

CREATE TABLE t2 (t2_pk NUMBER, num_col NUMBER);

INSERT INTO t2 (t2_pk, num_col) VALUES (1,1);
INSERT INTO t2 (t2_pk, num_col) VALUES (2,2);

COMMIT;

The direct way is likely to lead over a correlated subquery:

SQL>UPDATE  t1
  2  SET     num_col = (SELECT num_col
  3                     FROM   t2
  4                     WHERE  t1.t1_pk = t2.t2_pk)
  5  ;

2 rows updated.

Not difficult to see that this correlated subquery is executed once per row in T1. Depending on requirements, data, etc., this approach can be sometimes quite acceptable but sometimes undesirable. What alternatives are there? We look at two of them: update of a join and merge statement.

Update of a join

It’s about the concept of “updatable join views”. It’s well known, that one could update a view with a join behind it, even without implementing INSTEAD OF triggers. The modified table needs to be “key-preserved”. Less well known (or rarely used?) is the fact that SQL syntax allows us to use SQL query as a target for an UPDATE Statement, instead of physically creating a join view.

So we update the join:

SQL> UPDATE
  2  (SELECT t1.num_col t1_num_col
  3   ,      t2.num_col t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   )
  6  SET     t1_num_col = t2_num_col;
SET     t1_num_col = t2_num_col
        *
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved 
table

What’s going on here? This error prevents the data from being accidentally corrupted. Every record from the table to be modified must be updated at most once. If T2_PK is not unique in the table T2, then the records from T1 are multiplied in the result of the join too. To make sure it does not happen, you need a primary key or unique constraint on the T2. Then it works:

SQL> ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (t2_pk);

Table altered.

SQL> UPDATE
  2  (SELECT t1.num_col t1_num_col
  3   ,      t2.num_col t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   )
  6  SET     t1_num_col = t2_num_col;

2 rows updated.

ORA-01779 is a parse time error. It doesn’t depend on data but rather on schema. So it is, for example, not possible to generate an execution plan with EXPLAIN PLAN or to successfully compile a PL/SQL unit with this statement. And now it is clear what the hint BYPASS_UJVC does. “UJVC” probably stands for “updatable join view check” and this check does not take place with the hint. We delete the constraint and execute our update statement with the hint – there is no error message and records have been updated:

SQL> ALTER TABLE t2 DROP CONSTRAINT t2_pk DROP INDEX;

Table altered.

SQL>
SQL> UPDATE /*+ bypass_ujvc */
  2  (SELECT t1.num_col t1_num_col
  3   ,      t2.num_col t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   )
  6  SET     t1_num_col = t2_num_col;

2 rows updated.

But you must exactly know what you are doing here! You will not be warned any more, if your data leads to multiple updates. This can be well illustrated. We now add a record to the T2, so for T2_PK = 2 there are two records with values ​​2 and 3 now. What happens then? The update tells that three records were updated. But there are only two records in the T1? Right! And one of them has been updated twice! Why is the value 2 and not 3 (see line 19)? The answer is, it’s random, it is neither defined nor is it deterministic. If we change the execution plan with a hint (see line 23), the result changes (see line 36). To save space, I have omitted the execution plans, but the result is clear. What if we had trigger on the table T1 that fires on update of the column num_col and executes further actions? Possibly broken application logic.

SQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);

1 row created.

SQL> UPDATE /*+ bypass_ujvc */
  2  (SELECT t1.num_col t1_num_col
  3   ,      t2.num_col t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   )
  6  SET     t1_num_col = t2_num_col;

3 rows updated.

SQL>  SELECT * FROM t1;

     T1_PK    NUM_COL
---------- ----------
         1          1
         2          2

SQL>
SQL> UPDATE /*+ bypass_ujvc */
  2  (SELECT /*+ use_nl (t1 t2) */  t1.num_col t1_num_col
  3   ,      t2.num_col t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   )
  6  SET     t1_num_col = t2_num_col;

3 rows updated.

SQL>  SELECT * FROM t1;

     T1_PK    NUM_COL
---------- ----------
         1          1
         2          3

Aside from that, this hint is undocumented , so its function may change without warning, it may disappear in the next release, etc. As mentioned in the beginning, it actually happened to me a long time ago. There was a statement with this hint in production code. Then after an update to a patchset 9.2.x the hint had just stopped working with the ORA-01779 error. With the next patch set or the database software, the functioning of this hint was restored, but by then it was of course banished from production.

Merge

Another way to solve the original problem, is using a MERGE statement. As of Oracle 10g, “WHEN MATCHED” / “WHEN NOT MATCHED” clauses are optional, which makes the task even more easier. In fact, we want only to update records and not to insert and therefore do not need “WHEN NOT MATHCHED” clause.

MERGE requires no constraints, but what happens if we have have duplicates in T2 as in the previous example? MERGE protects us from this situation. The error, however, is different: ORA-30926: unable to get a stable set of rows in the source tables. Unlike ORA-01779, the error is reported only at run time, and only if duplicates are really there.

SQL> SELECT * FROM t2;

     T2_PK    NUM_COL
---------- ----------
         1          1
         2          2
SQL> MERGE INTO t1
  2  USING (SELECT  t2.t2_pk, t2.num_col
  3         FROM    t2) q
  4  ON    (t1.t1_pk = q.t2_pk)
  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;

2 rows merged.

SQL> SELECT * FROM t1;

     T1_PK    NUM_COL
---------- ----------
         1          1
         2          2

SQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);

1 row created.

SQL> MERGE INTO t1
  2  USING (SELECT  t2.t2_pk, t2.num_col
  3         FROM    t2) q
  4  ON    (t1.t1_pk = q.t2_pk)
  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;
MERGE INTO t1
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

If you expect duplicates, you need a strategy for dealing with them. It may be an aggregation with SUM () or MIN / MAX value. Then you can implement this strategy in USING clause. The MERGE statement is the winner in terms of readability, because it is not directly possible with an UPDATE. We get in a “non-updatable view”. The error message is: ORA-01732: data manipulation operation not legal on this view. You have to hide the aggregations, analytic functions, etc. in the subquery:

SQL> UPDATE /*+ bypass_ujvc */
  2  (SELECT t1.num_col t1_num_col
  3   ,      SUM(t2.num_col) t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   GROUP BY t1.t1_pk
  6   )
  7  SET     t1_num_col = t2_num_col;
(SELECT t1.num_col t1_num_col
*
ERROR at line 2:
ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE /*+ bypass_ujvc */
  2  (SELECT t1.num_col t1_num_col
  3   ,      sumt2.t2_num_col
  4   FROM   t1 JOIN
  5         (SELECT t2.t2_pk
  6         ,       SUM(t2.num_col) t2_num_col
  7         FROM    t2
  8         GROUP BY t2.t2_pk) sumt2
  9         ON t1.t1_pk = sumt2.t2_pk
 10   )
 11  SET     t1_num_col = t2_num_col;

2 rows updated.

...
--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | UPDATE STATEMENT      |      |
|   1 |  UPDATE               | T1   |
|   2 |   HASH JOIN           |      |
|   3 |    VIEW               |      |
|   4 |     SORT GROUP BY     |      |
|   5 |      TABLE ACCESS FULL| T2   |
|   6 |    TABLE ACCESS FULL  | T1   |
--------------------------------------

SQL> MERGE INTO t1
  2  USING (SELECT  t2.t2_pk, SUM(t2.num_col) num_col
  3         FROM    t2
  4         GROUP BY t2.t2_pk) q
  5  ON    (t1.t1_pk = q.t2_pk)
  6  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;

2 rows merged.

...
---------------------------------------
| Id  | Operation              | Name |
---------------------------------------
|   0 | MERGE STATEMENT        |      |
|   1 |  MERGE                 | T1   |
|   2 |   VIEW                 |      |
|   3 |    HASH JOIN           |      |
|   4 |     VIEW               |      |
|   5 |      SORT GROUP BY     |      |
|   6 |       TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL  | T1   |
---------------------------------------


SQL>

As you can see, the execution plans are almost identical in both cases, so it’s really about readbility here.

11gR2

There some some changes to the behavior described above in the Database 11g Release 2.
The hint /*+ BYPASS_UJVC */ not doesn’t prevent from getting ORA-01779 any more. A search on My Oracle support pages indicates that there is rather an intention this time.

Secondly, there is some kind of oddity in the behavior of a MERGE Statement. On the same data it will be sometimes executed without an error (and corrupts the data) but sometimes an error ORA-30926 will be thrown.

SQL> SELECT VERSION FROM V$INSTANCE;

VERSION
-----------------
11.2.0.2.0

SQL> UPDATE /*+ bypass_ujvc */
  2  (SELECT t1.num_col t1_num_col
  3   ,      t2.num_col t2_num_col
  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk
  5   )
  6  SET     t1_num_col = t2_num_col;
SET     t1_num_col = t2_num_col
        *
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved 
table

SQL> MERGE INTO t1
  2  USING (SELECT  t2.t2_pk, t2.num_col
  3         FROM    t2) q
  4  ON    (t1.t1_pk = q.t2_pk)
  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;

2 rows merged.

SQL>
SQL> SELECT * FROM t1;

     T1_PK    NUM_COL
---------- ----------
         1          1
         2          2

SQL>
SQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);

1 row created.

SQL>
SQL> MERGE INTO t1
  2  USING (SELECT  t2.t2_pk, t2.num_col
  3         FROM    t2) q
  4  ON    (t1.t1_pk = q.t2_pk)
  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;

3 rows merged.

SQL> SELECT * FROM t1;

     T1_PK    NUM_COL
---------- ----------
         1          1
         2          3

SQL> MERGE INTO t1
  2  USING (SELECT  t2.t2_pk, t2.num_col
  3         FROM    t2) q
  4  ON    (t1.t1_pk = q.t2_pk)
  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;
MERGE INTO t1
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

Until now I could not find any bug for Oracle on this issue, although the misbehavior is obvious. An Internet search confirmed that I was not the first and not the only one who has observed this behavior. I’m curious how it looks in 12c …

Conclusion

It is certainly a good idea, trying to avoid correlated subqueries or – even worse – self-made PL/SQL loops without bulk processing for complicated data updates. The options are for example a “one-pass” update of a join or MERGE Statement. I would consider update of a join only if “key-preserving” constraints are there. And of course I would never use the hint BYPASS_UJVC any more. And I would advise to remove it from production code as soon as possible.

The MERGE statement provides greater flexibility and readability for such data manipulation. Nevertheless I would think about the uniqueness of the data myself and consider necessary de-duplication in my query. The automatic detection of such error situation seems to be not reliable, at least in the 11gR2.

One thought on “BYE-BYE, BYPASS_UJVC!

  1. admin Post author

    Follow-up 12c:

    Unfortunately, the merge behavior still remains unpredictable in 12c.

    SQL> select version from v$instance;
     
    VERSION
    -----------------
    12.1.0.1.0
     
    SQL> MERGE INTO t1
      2   USING (SELECT  t2.t2_pk, t2.num_col
      3          FROM    t2) q
      4   ON    (t1.t1_pk = q.t2_pk)
      5   WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;
     
    2 rows merged.
     
    SQL>
    SQL>
    SQL> SELECT * FROM t1;
     
         T1_PK    NUM_COL
    ---------- ----------
             1          1
             2          2
     
    SQL>
    SQL>
    SQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);
     
    1 row created.
     
    SQL>
    SQL>  MERGE INTO t1
      2   USING (SELECT  t2.t2_pk, t2.num_col
      3          FROM    t2) q
      4   ON    (t1.t1_pk = q.t2_pk)
      5   WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;
     
    3 rows merged.
     
    SQL>
    SQL>
    SQL>
    SQL> SELECT * FROM t1;
     
         T1_PK    NUM_COL
    ---------- ----------
             1          1
             2          3
     
    SQL>
    SQL> MERGE INTO t1
      2    USING (SELECT  t2.t2_pk, t2.num_col
      3          FROM    t2) q
      4   ON    (t1.t1_pk = q.t2_pk)
      5    WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;
    MERGE INTO t1
    *
    ERROR at line 1:
    ORA-30926: unable to get a stable set of rows in the source tables
     
     
    SQL>
    SQL> SELECT * FROM t1;
     
         T1_PK    NUM_COL
    ---------- ----------
             1          1
             2          3
    

    The first merge after insert succeeds! Only the second merge reports an error but the data gets actually updated! It’s time to open a bug…

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>