Oracle can do some, well, strange things when fixing issues caused by the optimizer. For 10g releases up to 10.2.0.x Oracle chose to silently ignore a message and eliminate an outer join on the MERGE statement under certain conditions. Let’s examine this and see exactly what Oracle implements.
Occasionally in a 10046 trace file generated from 10g releases prior to 10.2.0.x and processed by the tkprof utility you may see something similar to this:
SELECT 1 FROM DUAL WHERE <some condition here>
Error encountered: ORA-00904
Where <some condition here> is the ‘ON’ condition from a MERGE statement executed by that session. That seems odd, I know, and Oracle silently ignores that ORA-00904 condition as it is never displayed when executing the MERGE. Why would Oracle do this? Looking at the 10053 trace file generated from the following example:
SQL> merge into t1_merge t1
2 using (select 1 merged_key, lpad('y', 21, 'y') xtra from dual) t2
3 on (t1.merge_key = t2.merged_key)
4 when matched then update set t1.xtra_stuff = t2.xtra
5 when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra);
1 row merged.
SQL>
We see the following:
PARSE ERROR #5:len=36 dep=1 uid=60 oct=3 lid=60 tim=1266258229533447 err=904
SELECT 1 FROM DUAL WHERE t1.merge_key = t2.merged_key
CLOSE #5:c=0,e=4,dep=1,type=0,tim=1266258229533578
Registered qb: MRG$1 0x84379c (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
...
====================== END SQL Statement Dump ======================
=====================
PARSING IN CURSOR #1 len=302 dep=0 uid=1158 oct=189 lid=1158 tim=33189168479100 hv=1190985157 ad='1ec71da8'
merge into t1_merge t1
using (select 1 merged_key, lpad('y', 21, 'y') xtra from dual) t2
on (t1.merge_key = t2.merged_key)
when matched then update set t1.xtra_stuff = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra)
END OF STMT
PARSE #1:c=40000,e=51569,p=4,cr=29,cu=0,mis=1,r=0,dep=0,og=2,tim=33189168479094
BINDS #1:
EXEC #1:c=10000,e=558,p=0,cr=7,cu=3,mis=0,r=1,dep=0,og=2,tim=33189168479778
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=2135111 tim=33189168479862
WAIT #1: nam='SQL*Net message from client' ela= 419 driver id=1650815232 #bytes=1 p3=0 obj#=2135111 tim=33189168480324
STAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='MERGE T1_MERGE (cr=7 pr=0 pw=0 time=425 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW (cr=7 pr=0 pw=0 time=178 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=7 pr=0 pw=0 time=175 us)'
STAT #1 id=4 cnt=1 pid=3 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=1 us)'
STAT #1 id=5 cnt=1 pid=3 pos=2 obj=2135111 op='TABLE ACCESS FULL T1_MERGE (cr=7 pr=0 pw=0 time=170 us)'
Notice there is no outer join in the plan steps. Additionally, if we look at the outline data we see:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
FIRST_ROWS(10)
OUTLINE_LEAF(@"SEL$5428C7F1")
MERGE(@"SEL$2")
MERGE(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"MRG$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"MRG$1" "from$_subquery$_008"@"MRG$1")
NO_ACCESS(@"MRG$1" "T2"@"MRG$1")
FULL(@"MRG$1" "T1"@"MRG$1")
LEADING(@"MRG$1" "from$_subquery$_008"@"MRG$1" "T2"@"MRG$1" "T1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T2"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
END_OUTLINE_DATA
*/
The outline data in these cases shows the outer join isn’t used, as Oracle re-writes the query into this:
merge into t1
using (select 1 merged_key, lpad('y', 21, 'y') xtra from dual) t2
on (1 = 0)
when matched then update set t1.xtra = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra);
Interestingly, the reason for this behavior is available in the online documentation:
To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause
condition. An example of a constant filter predicate is ON (0=1). Oracle Database recognizes such a predicate
and makes an unconditional insert of all source rows into the table. This approach is different from omitting
the merge_update_clause. In that case, the database still must perform a join. With constant filter predicate,
no join is performed.
This appears to be fixed in 10.2.0.x and later releases as the ORA-00904 error does not occur in either of the trace files. MOS document id 244055.1 lists this modification as an ‘enhancement’ in 10g. The document also lists that the bug that requires this work-around is fixed in 10.1.0.1.0. Even though 10g in all its forms is an old release there still may be sites using it.
In 11.2.0.4 this same construct generates no ORA-00904 error, nor does the ‘dummy’ query appear. Notice that the join is restored as evidenced by the execution plan:
----- Current SQL Statement for this session (sql_id=5zv0cdsdp0bf4) -----
merge into t1_merge t1
using (select 1 merged_key, lpad('y', 21, 'y') xtra from dual) t2
on (t1.merge_key = t2.merged_key)
when matched then update set t1.xtra_stuff = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.merged_key, t2.merged_key, t2.xtra)
sql_text_length=289
sql=merge into t1_merge t1
using (select 1 merged_key, lpad('y', 21, 'y') xtra from dual) t2
on (t1.merge_key = t2.merged_key)
when matched then update set t1.xtra_stuff = t2.xtra
when not matched then insert (merge_key, more_txt, xtra_stuff) values (t2.mer
sql=ged_key, t2.merged_key, t2.xtra)
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | MERGE STATEMENT | | | | 6 | |
| 1 | MERGE | T1_MERGE| | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 31 | 6 | 00:00:01 |
| 4 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 | 00:00:01 |
| 5 | TABLE ACCESS FULL | T1_MERGE| 1 | 29 | 4 | 00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - filter("T1"."MERGE_KEY"=CASE WHEN (ROWID IS NOT NULL) THEN 1 ELSE 1 END )
The outline data where this is fixed shows the join is used:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5428C7F1")
MERGE(@"SEL$2")
MERGE(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"MRG$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
NO_ACCESS(@"MRG$1" "from$_subquery$_008"@"MRG$1")
NO_ACCESS(@"MRG$1" "T2"@"MRG$1")
FULL(@"MRG$1" "T1"@"MRG$1")
LEADING(@"MRG$1" "from$_subquery$_008"@"MRG$1" "T2"@"MRG$1" "T1"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T2"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")
PQ_DISTRIBUTE(@"MRG$1" "T2"@"MRG$1" NONE NONE)
PQ_DISTRIBUTE(@"MRG$1" "T1"@"MRG$1" NONE NONE)
FULL(@"SEL$5428C7F1" "DUAL"@"SEL$3")
FULL(@"SEL$5428C7F1" "T1"@"SEL$2")
LEADING(@"SEL$5428C7F1" "DUAL"@"SEL$3" "T1"@"SEL$2")
USE_NL(@"SEL$5428C7F1" "T1"@"SEL$2")
END_OUTLINE_DATA
*/
Oracle has, apparently, seen the ‘error of its ways’ with this ‘enhancement’ and re-thought how MERGE should be executed. Of course you may be stuck running an older version of 10g and could be affected by this ‘enhancement’. Even migrating to 10.2.0.4 can eliminate this strange query from your MERGE implementations. Know that this isn’t really a performance problem, but it can be disconcerting to see such essentially useless query text in trace files you may generate. Knowing where it comes from hopefully will make it easier to assess performance issues requiring 10046 and 10053 trace files.
