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)
as
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
SELECT 1 FROM DUAL WHERE t1.x = t2.x
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 #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)
END OF STMT
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);

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
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:

    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"MRG$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      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")
    END_OUTLINE_DATA

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.

About these ads