Support Bypass Method When Merge Into Hit Index

Availability

This feature is available since MogDB 5.0.0.

Introduction

For improved performance, this feature supports the use of the Bypass method when MERGE INTO hits an index to improve the performance of MERGE INTO.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Constraints

The reference specified by USING must explicitly return a conditional record (e.g. sys_dummy view).
The ON condition must be able to hit the target table index in its entirety (the same way that IndexScan supports Bypass) and can only use deterministic values (e.g., constants or bound variables).
ON condition queries for partitioned tables only support the generation of execution plans that are deterministic to a sub-table.
MATCHED and NOT MATCHED cannot reference columns in tables (or views) referenced by USING.
Updating partitioned fields is not supported with UPDATE.

Syntax Description

MERGE INTO target_table [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause;
when_clause is:
{ WHEN MATCHED THEN merge_update |
  WHEN NOT MATCHED THEN merge_insert }

Example

-- Prepare data
create table tab_bypass(id integer, name text);
insert into tab_bypass select n,'name'||n from generate_series(1,100000) as foo(n);
create index on tab_bypass(id);
analyze tab_bypass;

-- Check for "[Bypass]" in the output.
explain (costs off)
merge into tab_bypass using sys_dummy on(id=1)
when matched then update set name=name||'matched'
when not matched then insert (id,name) values(1,'name01');
-- A sample output message is as follows
                   QUERY PLAN                   
------------------------------------------------
 [Bypass]
 Merge on tab_bypass
   ->  Nested Loop Left Join
         ->  Result
         ->  Index Scan using tab_bypass_id_idx on tab_bypass
               Index Cond: (id = 1)


-- Using prepare
prepare mystmt(integer) as
merge into tab_bypass using sys_dummy on(id=$1)
when matched then update set name=name||'matched'
when not matched then insert (id,name) values($1,'name'||$1);

-- Check for "[Bypass]" in the output.
explain (costs off)
execute mystmt(10);
-- A sample output message is as follows
                   QUERY PLAN                   
------------------------------------------------
 [Bypass]
 Merge on tab_bypass
   ->  Nested Loop Left Join
         ->  Result
         ->  Index Scan using tab_bypass_id_idx on tab_bypass
               Index Cond: (id = $1)

-- Clear resources
deallocate mystmt;