Silent ORA-904 on MERGE

From time to time looking at the tkprof’ed SQL traces I see this message:

The following statement encountered a error during parse:

SELECT 1 FROM DUAL WHERE some_predicate_here

Error encountered: ORA-00904

A some_predicate_here is the predicate from ON clause of a MERGE statement executed by the session. Up until recently I didn’t know why that happens, and since the error is not reported to the end-user there’s no big problem other than very little annoyance.

This is an example:

drop table t1 cascade constraints purge;
create table t1 (x, y, pad)
select rownum, mod(rownum, 100), lpad('x', 10, 'x') from dual
connect by level <= 1000;
exec dbms_stats.gather_table_stats(user, 't1')

exec dbms_session.session_trace_enable(false)
alter session set events '10053 trace name context forever, level 2';

merge into t1 
using (select 1 x, lpad('y', 10, 'y') pad from dual) t2
on (t1.x = t2.x)
when matched then update set t1.pad = t2.pad
when not matched then insert (x,y,pad) values (t2.x, t2.x, t2.pad);

alter session set events '10053 trace name context off';
exec dbms_session.session_trace_disable

And trace file excerpt:

PARSE ERROR #5:len=36 dep=1 uid=60 oct=3 lid=60 tim=1266258229533447 err=904
CLOSE #5:c=0,e=4,dep=1,type=0,tim=1266258229533578
Registered qb: MRG$1 0x84379c (PARSER)
====================== END SQL Statement Dump ======================
PARSING IN CURSOR #2 len=198 dep=0 uid=60 oct=189 lid=60 tim=1266258229984031 hv=3137650586 ad='3afa49c8' sqlid='aq4m8daxh9gwu'
merge into t1
using (select 1 x, lpad('y', 10, 'y') pad from dual) t2
on (t1.x = t2.x)
when matched then update set t1.pad = t2.pad
when not matched then insert (x,y,pad) values (t2.x, t2.x, t2.pad)
PARSE #2:c=45993,e=451460,p=0,cr=7,cu=0,mis=1,r=0,dep=0,og=1,plh=1461866594,tim=1266258229984030
EXEC #2:c=0,e=16490,p=0,cr=6,cu=3,mis=0,r=1,dep=0,og=1,plh=1461866594,tim=1266258230000570
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='MERGE  T1 (cr=6 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW  (cr=6 pr=0 pw=0 time=0 us)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=6 pr=0 pw=0 time=0 us cost=6 size=18 card=1)'
STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
STAT #2 id=5 cnt=1 pid=3 pos=2 obj=65766 op='TABLE ACCESS FULL T1 (cr=6 pr=0 pw=0 time=0 us cost=4 size=18 card=1)'
CLOSE #2:c=0,e=6,dep=0,type=0,tim=1266258230000987

Raw trace file says that dummy SELECT is executed right before the original query optimization begins, in the recursive context; but no other clues to why that is needed. After extensive searching over the MOS/web I managed to find the answer – and it was right here in the docs! 🙂

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.

Apparently constant filter predicate allows Oracle to structurally modify execution plan by removing the outer join. This is it:

SQL> explain plan for
  2  merge into t1
  3  using (select 1 x, lpad('y', 10, 'y') pad from dual) t2
  4  on (1 = 0)
  5  when matched then update set t1.pad = t2.pad
  6  when not matched then insert (x,y,pad) values (t2.x, t2.x, t2.pad);


SQL> select * from table(dbms_xplan.display);

Plan hash value: 3550614331

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | MERGE STATEMENT |      |     1 |    28 |     2   (0)| 00:00:01 |
|   1 |  MERGE          | T1   |       |       |            |          |
|   2 |   VIEW          |      |       |       |            |          |
|   3 |    FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |

10 rows selected.

Trace 10053 of the last query shows that CBO still costs the join (6 join orders were considered in my case), but includes hints to block it:

      NO_ACCESS(@"MRG$1" "from$_subquery$_007"@"MRG$1")
      NO_ACCESS(@"MRG$1" "T2"@"MRG$1")
      FULL(@"MRG$1" "T1"@"MRG$1")
      LEADING(@"MRG$1" "from$_subquery$_007"@"MRG$1" "T2"@"MRG$1" "T1"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "T2"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "T1"@"MRG$1")

According to the Doc ID 244055.1 the constant filter predicate is “an enhancement” of 10g. Well, I cannot say this is a good one since it’s implementation sucks:

  • additional query is executed on each hard parse of a MERGE statement
  • CBO is working strangely costing the join when it seems unnecessary
  • it is restricted to constant predicates only (you can’t just add 1=0 to an existing predicate)

In my view this “feature” should rather be part of join elimination.

One Comment

  1. Posted January 26, 2015 at 22:43 | Permalink | Reply

    Yes, that’s it. Usually it may be a little annoyance, but in combination with dynamic SQL and a lot of parsing pressure, this scenario equals library cache contention and cursor wait events of multiple kind.
    Thank you for collecting, interpreting AND SHARING this outstanding information.

One Trackback

  1. […] 13-Where does that select from dual comes from when doing merge insert ? Timur Akhmadeev-Silent ORA-904 on MERGE […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s