Wenn Sie häufiger 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öglicherweise haben Sie auch gemerkt, dass die Abfrage in diesem Fall länger dauert. Dreimal länger, um genauer zu sagen, denn die ganze Abfrage wird drei Mal ausgeführt, wie die folgdenden Tests zeigen.
Ich habe bereits über key-preserved tables und ORA-30926 bzw. ORA-01779 in meinem Beitrag zum BYPASS_UJVC-Hint geschrieben.
Es gibt mehrere Gründe für diesen Fehler. Der häufigste ist jedoch wohl folgender.
Laut Dokumentation ist Merge ein deterministisches Statement. Das heißt, 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:
CREATE TABLE t_source (id NUMBER, val NUMBER); CREATE TABLE t_target (id NUMBER, val NUMBER); INSERT INTO t_source VALUES (1,10); INSERT INTO t_source VALUES (2,10); INSERT INTO t_source VALUES (3,10); INSERT INTO t_target VALUES (1,1); INSERT INTO t_target VALUES (2,1); COMMIT;
Jetzt führen wir ein MERGE-Statement aus und führen die Datensätze aus T_SOURCE mit den Datensätzen aus T_TARGET zusammen. Wir erwarten erstmal keine Schwierigkeiten hier, da die Spalte ID in der T_SOURCE eindeutig ist.
ALTER SESSION SET STATISTICS_LEVEL = ALL; COLUMN SQL_ID NEW_VAL SQL_ID SET PAGESIZE 0 SET LINESIZE 300 -- First Merge without an error MERGE /*+ gather_plan_statistics */ /* MERGE_TEST_1 */ INTO t_target t USING t_source q ON (q.id = t.id) WHEN MATCHED THEN UPDATE SET t.val = t.val + q.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val); ROLLBACK; SELECT sql_id from v$sql vs WHERE VS.SQL_TEXT LIKE '%MERGE_TEST_1%' AND VS.SQL_TEXT NOT LIKE '%v$sql%'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST')); ... Plan hash value: 445881280 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.01 | 22 | | 1 | MERGE | T_TARGET | 1 | | 0 |00:00:00.01 | 22 | | 2 | VIEW | | 1 | | 3 |00:00:00.01 | 14 | |* 3 | HASH JOIN OUTER | | 1 | 3 | 3 |00:00:00.01 | 14 | | 4 | TABLE ACCESS FULL| T_SOURCE | 1 | 3 | 3 |00:00:00.01 | 7 | | 5 | TABLE ACCESS FULL| T_TARGET | 1 | 2 | 2 |00:00:00.01 | 7 | -------------------------------------------------------------------------------------------
Merken, alle Ausführungsschritte wurden nur einmal ausgeführt (Starts=1). Anzahl betroffenber Datensätze (Affected rows – A-Rows) sind auch nachvollziehbar: join von drei DAtensätzen in T_SOURCE mit zwei aus T_TARGET.
Nun erstellen wir eine Dublette in der T_SOURCE und wiederholen unsere Übung:
-- Now creating duplicate record in the source table which leads to ORA-30926 INSERT INTO t_source (id, val) VALUES (2,20); COMMIT; MERGE /*+ gather_plan_statistics */ /* MERGE_TEST_2 */ INTO t_target t USING t_source q ON (q.id = t.id) WHEN MATCHED THEN UPDATE SET t.val = t.val + q.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val); SELECT sql_id from v$sql vs WHERE VS.SQL_TEXT LIKE '%MERGE_TEST_2%' AND VS.SQL_TEXT NOT LIKE '%v$sql%'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST')); Plan hash value: 445881280 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 3 | | 0 |00:00:00.01 | 36 | | 1 | MERGE | T_TARGET | 3 | | 0 |00:00:00.01 | 36 | | 2 | VIEW | | 3 | | 10 |00:00:00.01 | 40 | |* 3 | HASH JOIN OUTER | | 3 | 4 | 10 |00:00:00.01 | 40 | | 4 | TABLE ACCESS FULL| T_SOURCE | 3 | 4 | 12 |00:00:00.01 | 21 | | 5 | TABLE ACCESS FULL| T_TARGET | 3 | 2 | 6 |00:00:00.01 | 19 | -------------------------------------------------------------------------------------------
Was passiert da? Achten Sie darauf, wie alle Schritte nun drei Mal ausgeführt wurden (starts=3). A-Rows für T_TARGET ist nun sechs (2 Datensätze x 3 Mal) und für T_SOURCE ist es 12 (nun 4 Datensätze x 3 Mal).
Ist das ein Problem von display_cursor oder macht Oracle tatsächlich die ganze Arbeit drei Mal?
Vielleicht können wir das prüfen, in dem wir einen Trigger und eine Funktion anlegen:
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER t_merge_test
BEFORE INSERT OR UPDATE ON t_target FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting ID='||:new.id||' new val='||:new.val);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Updating ID='||:new.id||' new val='||:new.val||', old val='||:old.val);
END IF;
END;
/
CREATE OR REPLACE FUNCTION test_func (id NUMBER, val NUMBER) RETURN NUMBER IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Querying ID '||id||' val '||val);
RETURN val;
END;
/
MERGE /*+ gather_plan_statistics */
/* MERGE_TEST_3 */
INTO t_target t
USING (SELECT id, test_func(id, val) val FROM t_source) q
ON (q.id = t.id)
WHEN MATCHED THEN UPDATE SET t.val = t.val + q.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);
Querying ID 1 val 10
Updating ID=1 new val=11, old val=1
Querying ID 2 val 20
Updating ID=2 new val=21, old val=1
Querying ID 2 val 10
Updating ID=2 new val=11, old val=1
Querying ID 2 val 10
Querying ID 1 val 10
Querying ID 2 val 20
Querying ID 2 val 10
Querying ID 3 val 10
Querying ID 1 val 10
Updating ID=1 new val=11, old val=1
Querying ID 2 val 20
Updating ID=2 new val=21, old val=1
Querying ID 2 val 10
Updating ID=2 new val=11, old val=1
SELECT DISTINCT FIRST_VALUE(sql_id) OVER (ORDER BY VS.LAST_ACTIVE_TIME DESC) sql_id
FROM v$sql vs
WHERE VS.SQL_TEXT LIKE '%MERGE_TEST_3%'
AND VS.SQL_TEXT NOT LIKE '%v$sql%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 3 | | 0 |00:00:00.07 | 645 | 11 |
| 1 | MERGE | T_TARGET | 3 | | 0 |00:00:00.07 | 645 | 11 |
| 2 | VIEW | | 3 | | 10 |00:00:00.07 | 646 | 11 |
|* 3 | HASH JOIN OUTER | | 3 | 4 | 10 |00:00:00.01 | 40 | 0 |
| 4 | TABLE ACCESS FULL| T_SOURCE | 3 | 4 | 12 |00:00:00.01 | 21 | 0 |
| 5 | TABLE ACCESS FULL| T_TARGET | 3 | 2 | 6 |00:00:00.01 | 19 | 0 |
----------------------------------------------------------------------------------------------------
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ätze 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öglichkeit Jonathan Lewis bei den CBO Days 2014 in Zürich, einem Event organisiert von meinem Arbeitgeber Trivadis, danach zu fragen. Natürlich hat mich seine Antwort auf die richtige Spur gebracht. Wir haben hier wohl mit „statement restarts“ zu tun. In diesem Fall sind sie sogar möglich mit nur einer Sitzung. Aber das werde ich in meinem nächsten Beitrag beschreiben.
Übrigens, es gibt eine Reihe von Bugs im Zusammenhang mit ORA-30926, siehe dazu MOS How to Troubleshoot ORA-30926 Errors? (Doc ID 471956.1)