{"id":31,"date":"2013-03-02T00:15:10","date_gmt":"2013-03-01T22:15:10","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=31"},"modified":"2013-07-15T13:51:52","modified_gmt":"2013-07-15T11:51:52","slug":"bye-bye-bypass_ujvc","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/bye-bye-bypass_ujvc\/","title":{"rendered":"BYE-BYE, BYPASS_UJVC!"},"content":{"rendered":"

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

Wof\u00fcr ist es genau?<\/strong><\/p>\n

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\u00e4tze aus der Tabelle T2 aktualisieren. Die Verbindung zwischen zwei Tabellen wird \u00fcber Spalten T1_PK und T2_PK hergestellt:<\/p>\n

 \r\nCREATE TABLE t1 (t1_pk NUMBER, num_col NUMBER);\r\n\r\nINSERT INTO t1 (t1_pk, num_col) VALUES (1,NULL);\r\nINSERT INTO t1 (t1_pk, num_col) VALUES (2,NULL);\r\n\r\nCREATE TABLE t2 (t2_pk NUMBER, num_col NUMBER);\r\n\r\nINSERT INTO t2 (t2_pk, num_col) VALUES (1,1);\r\nINSERT INTO t2 (t2_pk, num_col) VALUES (2,2);\r\n\r\nCOMMIT;\r\n<\/pre>\n
Der direkte Weg wird wahrscheinlich \u00fcber eine korrelierte Unterabfrage f\u00fchren:<\/p>\n
\r\nSQL>UPDATE  t1\r\n  2  SET     num_col = (SELECT num_col\r\n  3                     FROM   t2\r\n  4                     WHERE  t1.t1_pk = t2.t2_pk)\r\n  5  ;\r\n\r\n2 rows updated.\r\n<\/pre>\n
Hier f\u00e4llt auf, dass diese korrelierte Unterabfrage einmal pro Datensatz in T1 ausgef\u00fchrt wird. Je nach Anforderungen, Datenkonstellation, etc. kann der Ansatz durchaus akzeptabel sein oder unerw\u00fcnscht. Welche Alternativen gibt es denn? Wir schauen uns zwei davon an: Update von einem Join und Merge-Statement.<\/p>\n
Update vom Join<\/strong><\/p>\n
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“<\/a> 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.  <\/p>\n
Wir aktualisieren also den Join:<\/p>\n
\r\nSQL> UPDATE\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      t2.num_col t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   )\r\n  6  SET     t1_num_col = t2_num_col;\r\nSET     t1_num_col = t2_num_col\r\n        *\r\nERROR at line 6:\r\nORA-01779: cannot modify a column which maps to a non key-preserved \r\ntable\r\n<\/pre>\n
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\u00e4tze 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:<\/p>\n
\r\nSQL> ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (t2_pk);\r\n\r\nTable altered.\r\n\r\nSQL> UPDATE\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      t2.num_col t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   )\r\n  6  SET     t1_num_col = t2_num_col;\r\n\r\n2 rows updated.\r\n<\/pre>\n
Zu der Fehlermedlung ORA-01779 muss man sagen, dass sie noch beim Parsen kommt. Es ist also, z.B. nicht m\u00f6glich ein Ausf\u00fchrungsplan mit EXPLAIN PLAN zu generieren oder eine PL\/SQL-Einheit mit diesem  Statement erfolgreich zu kompilieren.
\nUnd nun wird klar, was der Hint BYPASS_UJVC macht. „UJVC“ steht wohl f\u00fcr „Updatable Join View Check“ und diese \u00dcberpr\u00fcfung findet nicht mehr statt. L\u00f6schen wir den Constraint wieder und setzen das Update diesmal mit dem eingebauten Hint ab – es kommt keine Fehlermeldung und Datens\u00e4tze werden aktualisiert:<\/p>\n
\r\nSQL> ALTER TABLE t2 DROP CONSTRAINT t2_pk DROP INDEX;\r\n\r\nTable altered.\r\n\r\nSQL>\r\nSQL> UPDATE \/*+ bypass_ujvc *\/\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      t2.num_col t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   )\r\n  6  SET     t1_num_col = t2_num_col;\r\n\r\n2 rows updated.\r\n\r\n<\/pre>\n
Aber Vorsicht! Man sollte hier genau wissen, was man tut. Man wird nicht mehr gewarnt, wenn Datenkonstellation zu mehrfachen Updates f\u00fchren. Das l\u00e4sst sich gut veranschaulichen. F\u00fcgen wir nun einen Datensatz in die T2, sodass es f\u00fcr T2_PK=2 jetzt zwei Datens\u00e4tze mit Werten 2 und 3 vorhanden sind. Was passiert dann? Das Update meldet, dass drei Datens\u00e4tze aktualisiert  w\u00e4ren. Es gibt aber in der T1 nur zwei Datens\u00e4tze? 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\u00fchrungsplan durch einen Hint ver\u00e4ndern (s. Zeile 23), \u00e4ndert sich das Ergebnis (s. Zeile 36). Um Platz zu sparen, habe ich die Ausf\u00fchrungspl\u00e4ne ausgelassen, aber das Ergebnis ist deutlich. Was w\u00e4re, wenn auf der Tabelle T1 Trigger definiert w\u00e4ren, die auf Update der Spalte NUM_COL reagierten und weitere Aktionen ausl\u00f6sten? Gebrochene Applikationslogik wom\u00f6glich.<\/p>\n
\r\nSQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);\r\n\r\n1 row created.\r\n\r\nSQL> UPDATE \/*+ bypass_ujvc *\/\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      t2.num_col t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   )\r\n  6  SET     t1_num_col = t2_num_col;\r\n\r\n3 rows updated.\r\n\r\nSQL>  SELECT * FROM t1;\r\n\r\n     T1_PK    NUM_COL\r\n---------- ----------\r\n         1          1\r\n         2          2\r\n\r\nSQL>\r\nSQL> UPDATE \/*+ bypass_ujvc *\/\r\n  2  (SELECT \/*+ use_nl (t1 t2) *\/  t1.num_col t1_num_col\r\n  3   ,      t2.num_col t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   )\r\n  6  SET     t1_num_col = t2_num_col;\r\n\r\n3 rows updated.\r\n\r\nSQL>  SELECT * FROM t1;\r\n\r\n     T1_PK    NUM_COL\r\n---------- ----------\r\n         1          1\r\n         2          3\r\n<\/pre>\n
Abgesehen davon, ist dieser Hint UNDOKUMENTIERT,<\/strong> d.h. seine Funktionsweise kann sich ohne Vorwarnung \u00e4ndern, der kann ganz verschwinden, etc. Wie anfangs erw\u00e4hnt, ist es mir auch tats\u00e4chlich 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\u00f6rt zu funktionieren. Die ORA-01779 Fehler kamen wieder. Mit dem n\u00e4chsten Patchset der Datenbank-Software war die Funktionsweise des Hints wiederhergestellt, aber der Hint war nat\u00fcrlich bis dahin schon lange aus der Produktion verbannt.<\/p>\n
Merge<\/strong><\/p>\n
Eine andere M\u00f6glichkeit, die urspr\u00fcngliche Aufgabe zu l\u00f6sen, 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\u00f6chten wir dabei nur Datens\u00e4tze aktualisieren und keinen neuen einf\u00fcgen und brauchen daher keine „WHEN NOT MATHCHED“ Klausel.<\/p>\n
Das Merge verlangt von uns keine Constraints, aber was passiert, wenn wir wie im Beispiel zuvor, Duplikate in T2 haben? MERGE sch\u00fctzt 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\u00e4chlich Duplikate vorkommen.<\/p>\n
\r\nSQL> SELECT * FROM t2;\r\n\r\n     T2_PK    NUM_COL\r\n---------- ----------\r\n         1          1\r\n         2          2\r\nSQL> MERGE INTO t1\r\n  2  USING (SELECT  t2.t2_pk, t2.num_col\r\n  3         FROM    t2) q\r\n  4  ON    (t1.t1_pk = q.t2_pk)\r\n  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;\r\n\r\n2 rows merged.\r\n\r\nSQL> SELECT * FROM t1;\r\n\r\n     T1_PK    NUM_COL\r\n---------- ----------\r\n         1          1\r\n         2          2\r\n\r\nSQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);\r\n\r\n1 row created.\r\n\r\nSQL> MERGE INTO t1\r\n  2  USING (SELECT  t2.t2_pk, t2.num_col\r\n  3         FROM    t2) q\r\n  4  ON    (t1.t1_pk = q.t2_pk)\r\n  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;\r\nMERGE INTO t1\r\n           *\r\nERROR at line 1:\r\nORA-30926: unable to get a stable set of rows in the source tables\r\n<\/pre>\n
Wenn man mit Duplikaten rechnet, muss man eine Strategie f\u00fcr 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\u00f6glich, 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“:<\/p>\n
\r\nSQL> UPDATE \/*+ bypass_ujvc *\/\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      SUM(t2.num_col) t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   GROUP BY t1.t1_pk\r\n  6   )\r\n  7  SET     t1_num_col = t2_num_col;\r\n(SELECT t1.num_col t1_num_col\r\n*\r\nERROR at line 2:\r\nORA-01732: data manipulation operation not legal on this view\r\n\r\nSQL> UPDATE \/*+ bypass_ujvc *\/\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      sumt2.t2_num_col\r\n  4   FROM   t1 JOIN\r\n  5         (SELECT t2.t2_pk\r\n  6         ,       SUM(t2.num_col) t2_num_col\r\n  7         FROM    t2\r\n  8         GROUP BY t2.t2_pk) sumt2\r\n  9         ON t1.t1_pk = sumt2.t2_pk\r\n 10   )\r\n 11  SET     t1_num_col = t2_num_col;\r\n\r\n2 rows updated.\r\n\r\n...\r\n--------------------------------------\r\n| Id  | Operation             | Name |\r\n--------------------------------------\r\n|   0 | UPDATE STATEMENT      |      |\r\n|   1 |  UPDATE               | T1   |\r\n|   2 |   HASH JOIN           |      |\r\n|   3 |    VIEW               |      |\r\n|   4 |     SORT GROUP BY     |      |\r\n|   5 |      TABLE ACCESS FULL| T2   |\r\n|   6 |    TABLE ACCESS FULL  | T1   |\r\n--------------------------------------\r\n\r\nSQL> MERGE INTO t1\r\n  2  USING (SELECT  t2.t2_pk, SUM(t2.num_col) num_col\r\n  3         FROM    t2\r\n  4         GROUP BY t2.t2_pk) q\r\n  5  ON    (t1.t1_pk = q.t2_pk)\r\n  6  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;\r\n\r\n2 rows merged.\r\n\r\n...\r\n---------------------------------------\r\n| Id  | Operation              | Name |\r\n---------------------------------------\r\n|   0 | MERGE STATEMENT        |      |\r\n|   1 |  MERGE                 | T1   |\r\n|   2 |   VIEW                 |      |\r\n|   3 |    HASH JOIN           |      |\r\n|   4 |     VIEW               |      |\r\n|   5 |      SORT GROUP BY     |      |\r\n|   6 |       TABLE ACCESS FULL| T2   |\r\n|   7 |     TABLE ACCESS FULL  | T1   |\r\n---------------------------------------\r\n\r\n\r\nSQL>\r\n<\/pre>\n
Da die Ausf\u00fchrungspl\u00e4ne in beiden F\u00e4llen beinah identisch sind, geht es hier wohl wirklich nur um Lesbrakeit.<\/p>\n
11gR2<\/strong><\/p>\n
Die Datenbank 11g Release 2 hat einige Ver\u00e4nderungen zum oben beschriebenen Verhalten mitgebracht. Zum einen, funktioniert der Hint \/*+ BYPASS_UJVC*\/<\/strong> nicht mehr. Eine Suche auf Oracle Support Seiten<\/a> deutet daraufhin, dass es diesmal eine Absicht ist.<\/p>\n
Zum zweiten, l\u00e4sst sich das Verhalten von MERGE nicht mehr so richtig nachvollziehen. Bei gleicher Datenkonstellation wird sporadisch mal das Statement ausgef\u00fchrt (Datenkorruption inklusive) mal der ORA-30926 berichtet.<\/p>\n
\r\nSQL> SELECT VERSION FROM V$INSTANCE;\r\n\r\nVERSION\r\n-----------------\r\n11.2.0.2.0\r\n\r\nSQL> UPDATE \/*+ bypass_ujvc *\/\r\n  2  (SELECT t1.num_col t1_num_col\r\n  3   ,      t2.num_col t2_num_col\r\n  4   FROM   t1 JOIN t2 ON t1.t1_pk = t2.t2_pk\r\n  5   )\r\n  6  SET     t1_num_col = t2_num_col;\r\nSET     t1_num_col = t2_num_col\r\n        *\r\nERROR at line 6:\r\nORA-01779: cannot modify a column which maps to a non key-preserved \r\ntable\r\n\r\nSQL> MERGE INTO t1\r\n  2  USING (SELECT  t2.t2_pk, t2.num_col\r\n  3         FROM    t2) q\r\n  4  ON    (t1.t1_pk = q.t2_pk)\r\n  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;\r\n\r\n2 rows merged.\r\n\r\nSQL>\r\nSQL> SELECT * FROM t1;\r\n\r\n     T1_PK    NUM_COL\r\n---------- ----------\r\n         1          1\r\n         2          2\r\n\r\nSQL>\r\nSQL> INSERT INTO t2 (t2_pk, num_col) VALUES (2,3);\r\n\r\n1 row created.\r\n\r\nSQL>\r\nSQL> MERGE INTO t1\r\n  2  USING (SELECT  t2.t2_pk, t2.num_col\r\n  3         FROM    t2) q\r\n  4  ON    (t1.t1_pk = q.t2_pk)\r\n  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;\r\n\r\n3 rows merged.\r\n\r\nSQL> SELECT * FROM t1;\r\n\r\n     T1_PK    NUM_COL\r\n---------- ----------\r\n         1          1\r\n         2          3\r\n\r\nSQL> MERGE INTO t1\r\n  2  USING (SELECT  t2.t2_pk, t2.num_col\r\n  3         FROM    t2) q\r\n  4  ON    (t1.t1_pk = q.t2_pk)\r\n  5  WHEN  MATCHED THEN UPDATE SET t1.num_col = q.num_col;\r\nMERGE INTO t1\r\n*\r\nERROR at line 1:\r\nORA-30926: unable to get a stable set of rows in the source tables\r\n\r\n<\/pre>\n
Bis jetzt konnte ich keinen Bug bei Oracle zu diesem Thema finden, obwohl das Fehlverhalten offensichtlich ist. Eine Suche im Internet best\u00e4tigt<\/a>, dass ich nicht der erste und nicht der einzige war, der dieses Verhalten beobachtet hat. Ich bin gespannt, wie das in 12c aussieht…<\/p>\n
Fazit<\/strong><\/p>\n
Sicherlich ist es zu begr\u00fc\u00dfen, 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\u00fcrde die Update-Variante nur in Betracht ziehen, wenn die ben\u00f6tigten Constraints existieren. Den Hint BYPASS_UJVC<\/strong> werde ich nicht mehr aktiv einsetzen. Aus dem produktiven Code geh\u00f6rt er sowieso schnellstm\u00f6glich heraus.
\nDas MERGE-Statement bietet f\u00fcr solche Datenmanipulationen mehr Flexibilit\u00e4t und Lesbarkeit. Einzig \u00fcber die Eindeutigkeit der selektierten Daten w\u00fcrde ich mir immer selber Gedanken machen und notfalls Deduplizierungsma\u00dfnahmen in meiner Abfrage ber\u00fccksichtigen. Auf die automatische Erkennung der Fehlersituation ist zumindest in der 11.2 kein Verlass.<\/p>\n","protected":false},"excerpt":{"rendered":"
Genau dieser Hint \/*+ BYPASS_UJVC *\/ hat mir vor gut zehn Jahren bereits einen Denkzettel f\u00fcr den richtigen Umgang mit undokumentierten Hints in produktiven Umgebungen verpasst. Zugegeben, habe ich ihn seitdem f\u00fcr Ad-hoc-Datenmanipulationen trotzdem ein paar Mal benutzt. Nun sorgt ein seltsames Verhalten in der 11.2 Datenbank daf\u00fcr, dass ich mir das Thema von „one-pass“ […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,22],"tags":[7,6,9,10,37,38,23,8],"class_list":["post-31","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql","category-trivadis","tag-bypass_ujvc","tag-hint","tag-join","tag-merge","tag-oracle","tag-sql","tag-trivadiscontent","tag-update"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/31","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/comments?post=31"}],"version-history":[{"count":49,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/31\/revisions"}],"predecessor-version":[{"id":184,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/31\/revisions\/184"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=31"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=31"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}