{"id":140,"date":"2015-04-06T20:54:43","date_gmt":"2015-04-06T18:54:43","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=140"},"modified":"2020-03-06T17:44:04","modified_gmt":"2020-03-06T15:44:04","slug":"merge-and-ora-30926","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926\/","title":{"rendered":"MERGE and ORA-30926"},"content":{"rendered":"\n<p>If you&#8217;ve ever used MERGE, then it is quite probable that you are familiar with ORA-30926 &#8211; unable to get a stable set of rows in the source tables. You have also probably noticed, that in case of hitting this error, your query takes longer. Three times longer, because the whole query is executed three times, as following tests show. <\/p>\n\n\n\n<!--more-->\n\n\n\n<p>I already wrote about <a title=\"Key-preserved tables\" href=\"http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e25494\/views001.htm#ADMIN11783\" target=\"_blank\" rel=\"noopener noreferrer\">key-preserved tables<\/a> and hitting the ORA-30926 or ORA-01779 in my post about <a title=\"BYPASS_UJVC hint\" href=\"https:\/\/blog.sqlora.com\/en\/2013\/03\/02\/bye-bye-bypass_ujvc\/\" target=\"_blank\" rel=\"noopener noreferrer\">BYPASS_UJVC hint.<\/a><\/p>\n\n\n\n<p>There are several possible reasons for this error. The most common is probably following.<\/p>\n\n\n\n<p>According to the documentation, <a title=\"Merge is a deterministic statement\" href=\"http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41084\/statements_9016.htm#SQLRF01606\" target=\"_blank\" rel=\"noopener noreferrer\">Merge is a deterministic statement.<\/a> It means you cannot update the same row of the target table multiple times in the same MERGE statement. If a table or a query in USING clause has duplicates with regard to the keys used in ON clause, and those records will match, thus are going to be updated multiple times, then you&#8217;ll get the ORA-30926 &#8211; unable to get a stable set of rows in the source tables. Let&#8217;s show this on the following test case:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TABLE t_source (id NUMBER, val NUMBER); \nCREATE TABLE t_target (id NUMBER, val NUMBER); \nINSERT INTO t_source VALUES (1,10); \nINSERT INTO t_source VALUES (2,10); \nINSERT INTO t_source VALUES (3,10); \nINSERT INTO t_target VALUES (1,1); \nINSERT INTO t_target VALUES (2,1); \nCOMMIT; \n<\/pre><\/div>\n\n\n<p>Now, we execute a MERGE statement, merging records from T_SOURCE into T_TARGET. We await no difficulties here, because ID in T_SOURCE is unique.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nALTER SESSION SET STATISTICS_LEVEL = ALL;\n\nCOLUMN SQL_ID NEW_VAL SQL_ID\nSET PAGESIZE 0\nSET LINESIZE 300\n\n-- First Merge without an error\n\nMERGE \/*+ gather_plan_statistics *\/\n\/* MERGE_TEST_1 *\/\nINTO t_target t\nUSING t_source q\nON (q.id = t.id)\nWHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val\nWHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);\n\nROLLBACK;\n\nSELECT sql_id from v$sql vs\nWHERE  VS.SQL_TEXT LIKE &#039;%MERGE_TEST_1%&#039;\nAND    VS.SQL_TEXT NOT LIKE &#039;%v$sql%&#039;;\n\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=&gt;&#039;&amp;sql_id&#039;, format=&gt;&#039;IOSTATS LAST&#039;));\n\n...\n\nPlan hash value: 445881280\n-------------------------------------------------------------------------------------------\n| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers  |\n-------------------------------------------------------------------------------------------\n|   0 | MERGE STATEMENT      |          |      1 |        |      0 |00:00:00.01 |      22  |\n|   1 |  MERGE               | T_TARGET |      1 |        |      0 |00:00:00.01 |      22  |\n|   2 |   VIEW               |          |      1 |        |      3 |00:00:00.01 |      14  |\n|*  3 |    HASH JOIN OUTER   |          |      1 |      3 |      3 |00:00:00.01 |      14  |\n|   4 |     TABLE ACCESS FULL| T_SOURCE |      1 |      3 |      3 |00:00:00.01 |       7  |\n|   5 |     TABLE ACCESS FULL| T_TARGET |      1 |      2 |      2 |00:00:00.01 |       7  |\n-------------------------------------------------------------------------------------------\n\n<\/pre><\/div>\n\n\n<p>Note that all steps of this execution plan were executed only once (Starts=1). Actual rows are also feasible: join three rows in T_SOURCE with two rows in T_TARGET.<\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n\n\n\n<p><\/p>\n\n\n\n<p>Now we&#8217;ll create a duplicate row in T_SOURCE and repeat our exercise:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- Now creating duplicate record in the source table which leads to ORA-30926\nINSERT INTO t_source (id, val) VALUES  (2,20);\n\nCOMMIT;\n\nMERGE \/*+ gather_plan_statistics *\/\n\/* MERGE_TEST_2 *\/\nINTO t_target t\nUSING t_source q\nON (q.id = t.id)\nWHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val\nWHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);\n\nSELECT sql_id from v$sql vs\nWHERE  VS.SQL_TEXT LIKE &#039;%MERGE_TEST_2%&#039;\nAND    VS.SQL_TEXT NOT LIKE &#039;%v$sql%&#039;;\n\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=&gt;&#039;&amp;sql_id&#039;, format=&gt;&#039;IOSTATS LAST&#039;));\n\nPlan hash value: 445881280\n\n-------------------------------------------------------------------------------------------\n| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time | Buffers |\n-------------------------------------------------------------------------------------------\n|   0 | MERGE STATEMENT      |          |      3 |        |      0 |00:00:00.01 |      36 |\n|   1 |  MERGE               | T_TARGET |      3 |        |      0 |00:00:00.01 |      36 |\n|   2 |   VIEW               |          |      3 |        |     10 |00:00:00.01 |      40 |\n|*  3 |    HASH JOIN OUTER   |          |      3 |      4 |     10 |00:00:00.01 |      40 |\n|   4 |     TABLE ACCESS FULL| T_SOURCE |      3 |      4 |     12 |00:00:00.01 |      21 |\n|   5 |     TABLE ACCESS FULL| T_TARGET |      3 |      2 |      6 |00:00:00.01 |      19 |\n-------------------------------------------------------------------------------------------\n\n\n<\/pre><\/div>\n\n\n<p>What&#8217;s going on here? Note, how all steps being executed three times (starts=3). A-Rows for T_TARGET is six (2 rows x 3 times) and for T_SOURCE is now 12 (now 4 rows x 3 times).<br>Is this a flaw of display_cursor or is Oracle in fact doing all the stuff three times?<\/p>\n\n\n\n<p>Maybe we can prove it, creating some triggers:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET SERVEROUTPUT ON\n\nCREATE OR REPLACE TRIGGER t_merge_test\nBEFORE INSERT OR UPDATE ON t_target FOR EACH ROW\nBEGIN\nIF INSERTING THEN\n  DBMS_OUTPUT.PUT_LINE(&#039;Inserting ID=&#039;||:new.id||&#039; new val=&#039;||:new.val);\nELSIF UPDATING THEN\n  DBMS_OUTPUT.PUT_LINE(&#039;Updating ID=&#039;||:new.id||&#039; new val=&#039;||:new.val||&#039;, old val=&#039;||:old.val);\nEND IF;\nEND;\n\/\n\nCREATE OR REPLACE FUNCTION test_func (id NUMBER, val NUMBER) RETURN NUMBER IS\nBEGIN\n  DBMS_OUTPUT.PUT_LINE(&#039;Querying ID &#039;||id||&#039; val &#039;||val);\n  RETURN val;\nEND;\n\/\n\nMERGE \/*+ gather_plan_statistics *\/\n\/* MERGE_TEST_3 *\/\nINTO t_target t\nUSING (SELECT id, test_func(id, val) val FROM t_source) q\nON (q.id = t.id)\nWHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val\nWHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);\n\nQuerying ID 1 val 10\nUpdating ID=1 new val=11, old val=1\nQuerying ID 2 val 20\nUpdating ID=2 new val=21, old val=1\nQuerying ID 2 val 10\nUpdating ID=2 new val=11, old val=1\nQuerying ID 2 val 10\nQuerying ID 1 val 10\nQuerying ID 2 val 20\nQuerying ID 2 val 10\nQuerying ID 3 val 10\nQuerying ID 1 val 10\nUpdating ID=1 new val=11, old val=1\nQuerying ID 2 val 20\nUpdating ID=2 new val=21, old val=1\nQuerying ID 2 val 10\nUpdating ID=2 new val=11, old val=1\n\nSELECT DISTINCT FIRST_VALUE(sql_id) OVER (ORDER BY VS.LAST_ACTIVE_TIME DESC) sql_id\nFROM   v$sql vs\nWHERE  VS.SQL_TEXT LIKE &#039;%MERGE_TEST_3%&#039;\nAND    VS.SQL_TEXT NOT LIKE &#039;%v$sql%&#039;;\n\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=&gt;&#039;&amp;sql_id&#039;, format=&gt;&#039;IOSTATS LAST&#039;));\n\n----------------------------------------------------------------------------------------------------\n| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |\n----------------------------------------------------------------------------------------------------\n|   0 | MERGE STATEMENT      |          |      3 |        |      0 |00:00:00.07 |     645 |     11 |\n|   1 |  MERGE               | T_TARGET |      3 |        |      0 |00:00:00.07 |     645 |     11 |\n|   2 |   VIEW               |          |      3 |        |     10 |00:00:00.07 |     646 |     11 |\n|*  3 |    HASH JOIN OUTER   |          |      3 |      4 |     10 |00:00:00.01 |      40 |      0 |\n|   4 |     TABLE ACCESS FULL| T_SOURCE |      3 |      4 |     12 |00:00:00.01 |      21 |      0 |\n|   5 |     TABLE ACCESS FULL| T_TARGET |      3 |      2 |      6 |00:00:00.01 |      19 |      0 |\n----------------------------------------------------------------------------------------------------\n\n<\/pre><\/div>\n\n\n<p>From the output of our function and trigger one could clearly see, that we were actually querying and updating until we found a row which would be updated twice, then re-querying all rows, then start updating again and finally throwing an error. Last year I&#8217;ve had an opportunity to ask <a title=\"Jonathan Lewis\" href=\"https:\/\/jonathanlewis.wordpress.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">Jonathan Lewis<\/a> at the CBO Days 2014 in Zurich, an event organized by <a title=\"Trivadis\" href=\"http:\/\/www.trivadis.com\" target=\"_blank\" rel=\"noopener noreferrer\">Trivadis,<\/a> the company I work for. Of course, his answer showed me the right direction to think. We are dealing with statement restarts here. In this case it is even possible with only one session. I&#8217;ll show this in my next post.<\/p>\n\n\n\n<p>There are also a couple of bugs related to ORA-30926, see MOS <a title=\"How to Troubleshoot ORA-30926 Errors? (Doc ID 471956.1)\" href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocContentDisplay?id=471956.1\" target=\"_blank\" rel=\"noopener noreferrer\">How to Troubleshoot ORA-30926 Errors? (Doc ID 471956.1)<\/a><\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n\n\n<div class=\"crp-list-container\"><h3 class=\"crp-list-title\">Related Posts<\/h3><ul class=\"crp-list\"><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/bye-bye-bypass_ujvc\/\" target=\"_blank\">BYE-BYE, BYPASS_UJVC!<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926-part-2-or-differences-in-write-consistency-between-update-and-merge\/\" target=\"_blank\">MERGE and ORA-30926 &#8211; Part 2 or differences in write consistency between update and merge<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/ora-30926-in-oracle-23ai-whats-changed\/\" target=\"_blank\">ORA-30926 in Oracle 23ai: What\u2019s Changed?<\/a><\/div><\/li><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>If you&#8217;ve ever used MERGE, then it is quite probable that you are familiar with ORA-30926 &#8211; unable to get a stable set of rows in the source tables. You have also probably noticed, that in case of hitting this error, your query takes longer. Three times longer, because the whole query is executed three [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,2,3],"tags":[45,6,27,47],"class_list":["post-140","post","type-post","status-publish","format-standard","hentry","category-merge","category-oracle","category-sql","tag-duplicates","tag-merge","tag-ora-30926","tag-statement-restart"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=140"}],"version-history":[{"count":21,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":1263,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/140\/revisions\/1263"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}