The "merge" statement select can't seem to use the "merge" plan for the select

[sladebe]

I have two tables with exactly the same columns for the unique clustered index (ASE 16.0 SP03 PL02). For a query like this, with a select with an outer join, the optimizer nicely uses the "merge join" strategy for the join:

select t1.gi,t1.type,t1.ent,t1.mydata,t2.mydata
from t1
left outer join t2 on
  t1.gi=t2.gi and t1.type=t2.type and t1.ent=t2.ent
where t2.mydata is null

but if I then try and run the merge statement (insert or update to t2 to make it match t1):

merge
  into  t2 as dest
  using t1 as src
  on src.gi=dest.gi and src.type=dest.type and src.ent=dest.ent  -- join condition
  when matched /* on join condition */ 
         and isnull(src.mydata,"")!=isnull(dest.mydata,"")
       then update set dest.mydata=src.mydata
  when not matched /* on join condition */
       then insert values( src.gi, src.type, src.ent, src.mydata )

The optimizer then implements a nested loop join, doing a table scan of t1 and a lookup of the clustered index for t2 for each row in t1:

       |   |MERGE Operator (VA = 3)
       |   |  The update mode is deferred.
       |   |
       |   |   |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left Outer Join)
       |   |   |
       |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |  FROM TABLE
       |   |   |   |  t1
       |   |   |   |  src
       |   |   |   |  Table Scan.
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning at start of table.
       |   |   |   |  Using I/O Size 16 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |   |
       |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |  FROM TABLE
       |   |   |   |  t2
       |   |   |   |  dest
       |   |   |   |  Using Clustered Index.
       |   |   |   |  Index : ucidx
       |   |   |   |  Forward Scan.
       |   |   |   |  Positioning by key.
       |   |   |   |  Keys are:
       |   |   |   |    gi ASC
       |   |   |   |    type ASC
       |   |   |   |    ent ASC
       |   |   |   |  Using I/O Size 16 Kbytes for data pages.
       |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
       |   |
       |   |  TO TABLE
       |   |  t2
       |   |  Using I/O Size 16 Kbytes for data pages.

So the nested loop join has to run one query against t2 for every row in t1, less than optimal. I want my "merge" insert/update to implement a "merge" select.

I tried using an abstract plan to force a merge_join (there are no index hints for merge statements):

PLAN'
( update ( m_join ( i_scan ucidx ( table ( src t1 ) ) )
                  ( i_scan ucidx ( table ( dest t2 ) ) ) ) )
'

but it gave the annoying message:

The children of the 'm_join ' operator don't have the required properties, as ordering, partitioning. The logical relational operation is valid at this point but it can not be implemented by this physical operator...

Meh. How can they not have the same ordering and partitioning? They have exactly the same clustered index columns and options.

Is this maybe just an optimizer insufficiency? Or is there some deeper reason why the merge join plan doesn't work?

For big tables, a merge join would be a significant improvement over a nested loop join (right?)

PS. I can open an incident with SAP if there's no obvious reason for this problem.

[sladebe]

I went ahead and opened an incident for this.

[sladebe]

FYI, this problem also occurs in 16.0 SP04