Connor McDonald on SQL and the Oracle Database

I wrote a few years back that for single row operations, MERGE might in fact have a large overhead than the do-it-yourself approach (ie, attempt an update, if it fails, then do an insert).

Just to show that it’s always good to revisit things as versions change, here’s the same demo (scaled up now because my laptop is faster )

As you can see, there is still a little difference between between the two operations. But even so, unless you need that absolute last little percentage of performance ** and **you know the data distribution (and hence probability of updates versus inserts extremely well, in particular, you’re expecting near always updates) perhaps MERGE is the way to go, in that it is more accurately reflects the operation being performed, and is hence closer to the concept of “self-documenting” code.

```
SQL> drop table t1 purge;
Table dropped.
SQL>
SQL> create table t1
2 ( x int primary key,
3 y int );
Table created.
SQL> -- all inserts
SQL>
SQL> set timing on
SQL> begin
2 for i in 1 .. 500000 loop
3 merge into t1
4 using ( select i x, i y
5 from dual ) m
6 on ( t1.x = m.x )
7 when matched then update
8 set t1.y = m.y
9 when not matched then
10 insert values (m.x, m.y );
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
```**Elapsed: 00:00:19.99**
SQL> -- 50/50 updates and inserts
SQL
SQL> set timing on
SQL> begin
2 for i in 250000 .. 750000 loop
3 merge into t1
4 using ( select i x, i y
5 from dual ) m
6 on ( t1.x = m.x )
7 when matched then update
8 set t1.y = m.y
9 when not matched then
10 insert values (m.x, m.y );
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
**Elapsed: 00:00:19.19**
SQL> -- all updates
SQL>
SQL> set timing on
SQL> begin
2 for i in 250000 .. 750000 loop
3 merge into t1
4 using ( select i x, i+1 y
5 from dual ) m
6 on ( t1.x = m.x )
7 when matched then update
8 set t1.y = m.y
9 when not matched then
10 insert values (m.x, m.y );
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
**Elapsed: 00:00:17.48**
SQL>
SQL> drop table t1 purge;
Table dropped.
Elapsed: 00:00:04.94
SQL>
SQL> create table t1
2 ( x int primary key,
3 y int );
Table created.
Elapsed: 00:00:00.01
SQL>
SQL> set timing on
SQL> begin
2 for i in 1 .. 500000 loop
3 update t1 set y = i where x = i;
4 if sql%notfound then insert into t1 values (i,i); end if;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
**Elapsed: 00:00:22.79**
SQL>
SQL> set timing on
SQL> begin
2 for i in 250000 .. 750000 loop
3 update t1 set y = i where x = i;
4 if sql%notfound then insert into t1 values (i,i); end if;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
**Elapsed: 00:00:18.08**
SQL>
SQL> set timing on
SQL> begin
2 for i in 250000 .. 750000 loop
3 update t1 set y = i+1 where x = i;
4 if sql%notfound then insert into t1 values (i,i+1); end if;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
**Elapsed: 00:00:13.88**
SQL>
SQL>
SQL>

%d bloggers like this:

Another niche consideration:

https://orastory.wordpress.com/2011/10/13/concurrent-merge/