BYE-BYE, BYPASS_UJVC!

Genau dieser Hint /*+ BYPASS_UJVC */ hat mir vor gut zehn Jahren bereits einen Denkzettel für den richtigen Umgang mit undokumentierten Hints in produktiven Umgebungen verpasst. Zugegeben, habe ich ihn seitdem für Ad-hoc-Datenmanipulationen trotzdem ein paar Mal benutzt. Nun sorgt ein seltsames Verhalten in der 11.2 Datenbank dafür, dass ich mir das Thema von “one-pass” Join-Updates und die Alternative mit Merge nochmal anschauen wollte. 

Wofür ist es genau?

Um zu verstehen, wie der Hint funktioniert, konstruieren wir uns ein einfaches Beispiel. Wir haben zwei Tabellen T1 und T2 und wollen eine numerische Spalte NUM_COL in der T1 mit den Daten entsprechender Datensätze aus der Tabelle T2 aktualisieren. Die Verbindung zwischen zwei Tabellen wird über Spalten T1_PK und T2_PK hergestellt:

 
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;

Der direkte Weg wird wahrscheinlich über eine korrelierte Unterabfrage führen:

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.

Hier fällt auf, dass diese korrelierte Unterabfrage einmal pro Datensatz in T1 ausgeführt wird. Je nach Anforderungen, Datenkonstellation, etc. kann der Ansatz durchaus akzeptabel sein oder unerwünscht. Welche Alternativen gibt es denn? Wir schauen uns zwei davon an: Update von einem Join und Merge-Statement.

Update vom Join

Es geht hier um das Konzept von “Updatable Join Views”. Vielen ist bekannt, dass man Views mit einem Join dahinter auch direkt aktualisieren kann, also ohne die INSTEAD OF Trigger zu implementieren. Die zu aktualisierende Tabelle muss nur dabei “key-preserved” sein. Weniger bekannt (oder seltener benutzt?) ist die Tatsache, dass SQL-Syntax es schon lange erlaubt, statt eine View physikalisch anzulegen, die SQL-Abfrage als Ziel eines UPDATE-Statements direkt zu nutzen.

Wir aktualisieren also den 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

Was ist los hier? Diese Fehlermeldung verhindert, dass die Daten versehentlich korrupt werden. Bei dieser Art von Update, muss es sichergestellt werden, dass jeder Datensatz in der zu aktualisierenden Tabelle nur einmalig aktualisiert wird. Soll die T2_PK in der Tabelle T2 nicht eindeutig sein, werden Datensätze aus T1 im Ergebnis vom Join vervielfacht. Um sicherzustellen, dass es nicht passiert, braucht man ein Primary Key oder Unique Constraint auf der T2. Dann funktioniert es:

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.

Zu der Fehlermedlung ORA-01779 muss man sagen, dass sie noch beim Parsen kommt. Es ist also, z.B. nicht möglich ein Ausführungsplan mit EXPLAIN PLAN zu generieren oder eine PL/SQL-Einheit mit diesem Statement erfolgreich zu kompilieren.
Und nun wird klar, was der Hint BYPASS_UJVC macht. “UJVC” steht wohl für “Updatable Join View Check” und diese Überprüfung findet nicht mehr statt. Löschen wir den Constraint wieder und setzen das Update diesmal mit dem eingebauten Hint ab – es kommt keine Fehlermeldung und Datensätze werden aktualisiert:

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.

Aber Vorsicht! Man sollte hier genau wissen, was man tut. Man wird nicht mehr gewarnt, wenn Datenkonstellation zu mehrfachen Updates führen. Das lässt sich gut veranschaulichen. Fügen wir nun einen Datensatz in die T2, sodass es für T2_PK=2 jetzt zwei Datensätze mit Werten 2 und 3 vorhanden sind. Was passiert dann? Das Update meldet, dass drei Datensätze aktualisiert wären. Es gibt aber in der T1 nur zwei Datensätze? Richtig! Und einer davon wurde zwei mal aktualisiert! Warum ist der Wert 2 und nicht 3 (siehe Zeile 19)? Die Antwort ist: Zufall, es ist nicht definiert und nicht deterministisch. Wenn wir den Ausführungsplan durch einen Hint verändern (s. Zeile 23), ändert sich das Ergebnis (s. Zeile 36). Um Platz zu sparen, habe ich die Ausführungspläne ausgelassen, aber das Ergebnis ist deutlich. Was wäre, wenn auf der Tabelle T1 Trigger definiert wären, die auf Update der Spalte NUM_COL reagierten und weitere Aktionen auslösten? Gebrochene Applikationslogik womöglich.

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

Abgesehen davon, ist dieser Hint UNDOKUMENTIERT, d.h. seine Funktionsweise kann sich ohne Vorwarnung ändern, der kann ganz verschwinden, etc. Wie anfangs erwähnt, ist es mir auch tatsächlich vor langer Zeit passiert. Es gab eine Stelle mit diesem Hint im Produktionscode. Dann hat der Hint nach einem Update auf ein Patchset 9.2.X einfach aufgehört zu funktionieren. Die ORA-01779 Fehler kamen wieder. Mit dem nächsten Patchset der Datenbank-Software war die Funktionsweise des Hints wiederhergestellt, aber der Hint war natürlich bis dahin schon lange aus der Produktion verbannt.

Merge

Eine andere Möglichkeit, die ursprüngliche Aufgabe zu lösen, ist der Einsatz von einem MERGE-Statement. Ab Oracle 10g sind die “WHEN MATCHED” / “WHEN NOT MATCHED” Klausel optional, was die Aufgabe noch mehr vereinfacht. In der Tat, möchten wir dabei nur Datensätze aktualisieren und keinen neuen einfügen und brauchen daher keine “WHEN NOT MATHCHED” Klausel.

Das Merge verlangt von uns keine Constraints, aber was passiert, wenn wir wie im Beispiel zuvor, Duplikate in T2 haben? MERGE schützt uns vor dieser Situation. Die Fehlermeldung ist allerdings eine andere: ORA-30926:unable to get a stable set of rows in the source tables. Im Gegensatz zu ORA-01779 wird der Fehler erst zur Laufzeit berichtet, wenn tatsächlich Duplikate vorkommen.

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

Wenn man mit Duplikaten rechnet, muss man eine Strategie für den Umgang damit parat haben. Es kann, z.B. eine Aggregation mit SUM() oder der MIN/MAX Wert sein. Dann kann man diese Strategie in der USING-Query ausformulieren. Aus dem Gesichtspunkt der Lesbarkeit gewinnt hier das MERGE-Statement. In der UPDATE-Variante ist es nicht direkt möglich, weil wir dadurch eine “non-updatable view” bekommen. Die Fehlermeldung ist: ORA-01732: data manipulation operation not legal on this view. Man muss die Aggregationen, analytische Funktionen, etc. in der Unterabfrage “verstecken”:

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>

Da die Ausführungspläne in beiden Fällen beinah identisch sind, geht es hier wohl wirklich nur um Lesbrakeit.

11gR2

Die Datenbank 11g Release 2 hat einige Veränderungen zum oben beschriebenen Verhalten mitgebracht. Zum einen, funktioniert der Hint /*+ BYPASS_UJVC*/ nicht mehr. Eine Suche auf Oracle Support Seiten deutet daraufhin, dass es diesmal eine Absicht ist.

Zum zweiten, lässt sich das Verhalten von MERGE nicht mehr so richtig nachvollziehen. Bei gleicher Datenkonstellation wird sporadisch mal das Statement ausgeführt (Datenkorruption inklusive) mal der ORA-30926 berichtet.

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

Bis jetzt konnte ich keinen Bug bei Oracle zu diesem Thema finden, obwohl das Fehlverhalten offensichtlich ist. Eine Suche im Internet bestätigt, dass ich nicht der erste und nicht der einzige war, der dieses Verhalten beobachtet hat. Ich bin gespannt, wie das in 12c aussieht…

Fazit

Sicherlich ist es zu begrüßen, wenn man bei komplizierten Datenaktualisierungen versucht, auf die korrelierte Unterabfragen oder – noch schlimmer – selbst gebauten Schleifen in PL/SQL zu verzichten. Eine Alternative ist ein “one-pass” Update oder Merge. Ich würde die Update-Variante nur in Betracht ziehen, wenn die benötigten Constraints existieren. Den Hint BYPASS_UJVC werde ich nicht mehr aktiv einsetzen. Aus dem produktiven Code gehört er sowieso schnellstmöglich heraus.
Das MERGE-Statement bietet für solche Datenmanipulationen mehr Flexibilität und Lesbarkeit. Einzig über die Eindeutigkeit der selektierten Daten würde ich mir immer selber Gedanken machen und notfalls Deduplizierungsmaßnahmen in meiner Abfrage berücksichtigen. Auf die automatische Erkennung der Fehlersituation ist zumindest in der 11.2 kein Verlass.

Ein Gedanke zu „BYE-BYE, BYPASS_UJVC!

  1. admin Artikelautor

    Follow-up 12c:

    In 12c bleibt es leider dabei: das verhalten von Merge ist inkonsistent und unvorhersehbar.

    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
    
    
    

    Das erste Merge nach dem Einfügen einer Dublette geht durch und erst beim zweiten Merge bekommen wir einen Fehler. Zeit einen Bug bei Oracle zu öffnen…

    Antworten

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *


*

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>