Wenn Sie h\u00e4ufiger mal ein MERGE statment verwendet haben, dann kommt Ihnen der folgende Fehler wahrscheinlich bekannt vor: ORA-30926 – unable to get a stable set of rows in the source tables. M\u00f6glicherweise haben Sie auch gemerkt, dass die Abfrage in diesem Fall l\u00e4nger dauert. Dreimal l\u00e4nger, um genauer zu sagen, denn die ganze Abfrage wird drei Mal ausgef\u00fchrt, wie die folgdenden Tests zeigen. <\/p>\n
Ich habe bereits \u00fcber key-preserved tables<\/a> und ORA-30926 bzw. ORA-01779 in meinem Beitrag zum BYPASS_UJVC-Hint<\/a> geschrieben.<\/p>\n Es gibt mehrere Gr\u00fcnde f\u00fcr diesen Fehler. Der h\u00e4ufigste ist jedoch wohl folgender.<\/p>\n Laut Dokumentation ist Merge ein deterministisches Statement.<\/a> Das hei\u00dft, man kann einen Datensatz nicht mehrfach mit einem MERGE aktualisieren. Wenn die Tabelle oder Abfrage in USING-Klausel bezogen auf die Spalten in der ON-Klausel Dubletten aufweist, bekommen Sie folgenden Fehler: ORA-30926 – unable to get a stable set of rows in the source tables. Wir zeigen das am folgenden Beispiel:<\/p>\n Jetzt f\u00fchren wir ein MERGE-Statement aus und f\u00fchren die Datens\u00e4tze aus T_SOURCE mit den Datens\u00e4tzen aus T_TARGET zusammen. Wir erwarten erstmal keine Schwierigkeiten hier, da die Spalte ID in der T_SOURCE eindeutig ist.<\/p>\n Merken, alle Ausf\u00fchrungsschritte wurden nur einmal ausgef\u00fchrt (Starts=1). Anzahl betroffenber Datens\u00e4tze (Affected rows – A-Rows) sind auch nachvollziehbar: join von drei DAtens\u00e4tzen in T_SOURCE mit zwei aus T_TARGET.<\/p>\n Nun erstellen wir eine Dublette in der T_SOURCE und wiederholen unsere \u00dcbung:<\/p>\n Was passiert da? Achten Sie darauf, wie alle Schritte nun drei Mal ausgef\u00fchrt wurden (starts=3). A-Rows f\u00fcr T_TARGET ist nun sechs (2 Datens\u00e4tze x 3 Mal) und f\u00fcr T_SOURCE ist es 12 (nun 4 Datens\u00e4tze x 3 Mal). Vielleicht k\u00f6nnen wir das pr\u00fcfen, in dem wir einen Trigger und eine Funktion anlegen:<\/p>\n Aus dem Output von der Funktion und Trigger kann man eindeutig erkennen: wir fragen T_SOURCE ab und aktualisieren T_TARGET, bis wir an dem Datensatz ankommen, der zum zweiten Mal aktualisiert werden soll, dann werden alle Datens\u00e4tze noch einmal abgefragt und dann fangen wir wieder an, abzufragen und zu aktualisieren, bis an dem problematischen Datensatz letztendlich der Fehler geschmissen wird. Warum passiert so was? Letztes Jahr hatte ich die M\u00f6glichkeit Jonathan Lewis<\/a> bei den CBO Days 2014 in Z\u00fcrich, einem Event organisiert von meinem Arbeitgeber Trivadis,<\/a> danach zu fragen. Nat\u00fcrlich hat mich seine Antwort auf die richtige Spur gebracht. Wir haben hier wohl mit „statement restarts“<\/strong> zu tun. In diesem Fall sind sie sogar m\u00f6glich mit nur einer Sitzung. Aber das werde ich in meinem n\u00e4chsten Beitrag beschreiben.<\/p>\n\r\nCREATE TABLE t_source (id NUMBER, val NUMBER);\r\nCREATE TABLE t_target (id NUMBER, val NUMBER);\r\n\r\nINSERT INTO t_source VALUES (1,10);\r\nINSERT INTO t_source VALUES (2,10);\r\nINSERT INTO t_source VALUES (3,10);\r\n\r\nINSERT INTO t_target VALUES (1,1);\r\nINSERT INTO t_target VALUES (2,1);\r\n\r\nCOMMIT;\r\n<\/pre>\n
\r\nALTER SESSION SET STATISTICS_LEVEL = ALL;\r\n\r\nCOLUMN SQL_ID NEW_VAL SQL_ID\r\nSET PAGESIZE 0\r\nSET LINESIZE 300\r\n\r\n-- First Merge without an error\r\n\r\nMERGE \/*+ gather_plan_statistics *\/\r\n\/* MERGE_TEST_1 *\/\r\nINTO t_target t\r\nUSING t_source q\r\nON (q.id = t.id)\r\nWHEN MATCHED THEN UPDATE SET t.val = t.val + q.val\r\nWHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);\r\n\r\nROLLBACK;\r\n\r\nSELECT sql_id from v$sql vs\r\nWHERE VS.SQL_TEXT LIKE '%MERGE_TEST_1%'\r\nAND VS.SQL_TEXT NOT LIKE '%v$sql%';\r\n\r\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));\r\n\r\n...\r\n\r\nPlan hash value: 445881280\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\r\n-------------------------------------------------------------------------------------------\r\n| 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.01 | 22 |\r\n| 1 | MERGE | T_TARGET | 1 | | 0 |00:00:00.01 | 22 |\r\n| 2 | VIEW | | 1 | | 3 |00:00:00.01 | 14 |\r\n|* 3 | HASH JOIN OUTER | | 1 | 3 | 3 |00:00:00.01 | 14 |\r\n| 4 | TABLE ACCESS FULL| T_SOURCE | 1 | 3 | 3 |00:00:00.01 | 7 |\r\n| 5 | TABLE ACCESS FULL| T_TARGET | 1 | 2 | 2 |00:00:00.01 | 7 |\r\n-------------------------------------------------------------------------------------------\r\n<\/pre>\n
\r\n-- Now creating duplicate record in the source table which leads to ORA-30926\r\nINSERT INTO t_source (id, val) VALUES (2,20);\r\n\r\nCOMMIT;\r\n\r\nMERGE \/*+ gather_plan_statistics *\/\r\n\/* MERGE_TEST_2 *\/\r\nINTO t_target t\r\nUSING t_source q\r\nON (q.id = t.id)\r\nWHEN MATCHED THEN UPDATE SET t.val = t.val + q.val\r\nWHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);\r\n\r\nSELECT sql_id from v$sql vs\r\nWHERE VS.SQL_TEXT LIKE '%MERGE_TEST_2%'\r\nAND VS.SQL_TEXT NOT LIKE '%v$sql%';\r\n\r\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));\r\n\r\nPlan hash value: 445881280\r\n\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time\t| Buffers |\r\n-------------------------------------------------------------------------------------------\r\n| 0 | MERGE STATEMENT | | 3 | | 0 |00:00:00.01 | 36 |\r\n| 1 | MERGE | T_TARGET | 3 | | 0 |00:00:00.01 | 36 |\r\n| 2 | VIEW | | 3 | | 10 |00:00:00.01 | 40 |\r\n|* 3 | HASH JOIN OUTER |\t | 3 | 4 | 10 |00:00:00.01 | 40 |\r\n| 4 | TABLE ACCESS FULL| T_SOURCE | 3 | 4 | 12 |00:00:00.01 | 21 |\r\n| 5 | TABLE ACCESS FULL| T_TARGET | 3 | 2 | 6 |00:00:00.01 | 19 |\r\n-------------------------------------------------------------------------------------------\r\n\r\n<\/pre>\n
\nIst das ein Problem von display_cursor oder macht Oracle tats\u00e4chlich die ganze Arbeit drei Mal?<\/p>\n\r\nSET SERVEROUTPUT ON\r\n\r\nCREATE OR REPLACE TRIGGER t_merge_test\r\nBEFORE INSERT OR UPDATE ON t_target FOR EACH ROW\r\nBEGIN\r\nIF INSERTING THEN\r\n DBMS_OUTPUT.PUT_LINE('Inserting ID='||:new.id||' new val='||:new.val);\r\nELSIF UPDATING THEN\r\n DBMS_OUTPUT.PUT_LINE('Updating ID='||:new.id||' new val='||:new.val||', old val='||:old.val);\r\nEND IF;\r\nEND;\r\n\/\r\n\r\nCREATE OR REPLACE FUNCTION test_func (id NUMBER, val NUMBER) RETURN NUMBER IS\r\nBEGIN\r\n DBMS_OUTPUT.PUT_LINE('Querying ID '||id||' val '||val);\r\n RETURN val;\r\nEND;\r\n\/\r\n\r\nMERGE \/*+ gather_plan_statistics *\/\r\n\/* MERGE_TEST_3 *\/\r\nINTO t_target t\r\nUSING (SELECT id, test_func(id, val) val FROM t_source) q\r\nON (q.id = t.id)\r\nWHEN MATCHED THEN UPDATE SET t.val = t.val + q.val\r\nWHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);\r\n\r\nQuerying ID 1 val 10\r\nUpdating ID=1 new val=11, old val=1\r\nQuerying ID 2 val 20\r\nUpdating ID=2 new val=21, old val=1\r\nQuerying ID 2 val 10\r\nUpdating ID=2 new val=11, old val=1\r\nQuerying ID 2 val 10\r\nQuerying ID 1 val 10\r\nQuerying ID 2 val 20\r\nQuerying ID 2 val 10\r\nQuerying ID 3 val 10\r\nQuerying ID 1 val 10\r\nUpdating ID=1 new val=11, old val=1\r\nQuerying ID 2 val 20\r\nUpdating ID=2 new val=21, old val=1\r\nQuerying ID 2 val 10\r\nUpdating ID=2 new val=11, old val=1\r\n\r\nSELECT DISTINCT FIRST_VALUE(sql_id) OVER (ORDER BY VS.LAST_ACTIVE_TIME DESC) sql_id\r\nFROM v$sql vs\r\nWHERE VS.SQL_TEXT LIKE '%MERGE_TEST_3%'\r\nAND VS.SQL_TEXT NOT LIKE '%v$sql%';\r\n\r\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));\r\n\r\n----------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |\r\n----------------------------------------------------------------------------------------------------\r\n| 0 | MERGE STATEMENT | | 3 | | 0 |00:00:00.07 | 645 | 11 |\r\n| 1 | MERGE | T_TARGET | 3 | | 0 |00:00:00.07 | 645 | 11 |\r\n| 2 | VIEW | | 3 | | 10 |00:00:00.07 | 646 | 11 |\r\n|* 3 | HASH JOIN OUTER | | 3 | 4 | 10 |00:00:00.01 | 40 | 0 |\r\n| 4 | TABLE ACCESS FULL| T_SOURCE | 3 | 4 | 12 |00:00:00.01 | 21 | 0 |\r\n| 5 | TABLE ACCESS FULL| T_TARGET | 3 | 2 | 6 |00:00:00.01 | 19 | 0 |\r\n----------------------------------------------------------------------------------------------------\r\n<\/pre>\n