merge into constructorresults tgt
using (select rsl.raceid as raceid
, rsl.constructorid as constructorid
, sum( rsl.points ) as points
from f1data.results rsl
where rsl.raceid = g_raceids(indx).raceid
and rsl.constructorid = g_raceids(indx).constructorid
group by rsl.raceid
, rsl.constructorid) src
on ( tgt.raceid = src.raceid
and tgt.constructorid = src.constructorid )
when matched
then
update
set tgt.points = src.points
when not matched
then
insert
( constructorresultsid
, raceid
, constructorid
, points)
values
( f1data.constructorresults_seq.nextval
, src.raceid
, src.constructorid
, src.points)
/
$>create or replace directory data_dir as '/tmp/'
2 /
Directory created.
$> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2) ,
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /
Table created.
$> create table emp
2 as
3 select * from scott.emp;
Table created.
$> delete from emp where mod(empno,2) = 1
2 /
4 rows deleted.
$> update emp set sal = sal/2
2 /
10 rows updated.
$> commit;
Commit complete.
$> select empno, sal from emp
2 MINUS
3 select empno, sal from external_table
4 /
EMPNO SAL
---------- ----------
7566 1487.5
7654 625
7698 1425
7782 1225
7788 1500
7844 750
7876 550
7900 475
7902 1500
7934 650
10 rows selected.
$> select empno, sal from external_table
2 MINUS
3 select empno, sal from emp
4 /
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
$> merge into EMP e1
2 using EXTERNAL_TABLE e2
3 on ( e2.empno = e1.empno )
4 when matched then
5 update set e1.sal = e2.sal
6 when not matched then
7 insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
8 values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno )
9 /
14 rows merged.
$> commit;
Commit complete.
$> select empno, sal from emp
2 MINUS
3 select empno, sal from external_table
4 /
no rows selected
$> select empno, sal from external_table
2 MINUS
3 select empno, sal from emp
4 /
no rows selected
I would seriously hope not a SINGLE index was used anywhere for a merge of a large set into a large set!!!!
a merge with a "when matched and when NOT matched" clause is alot like an outer join:
select *
from source left outer join target on (join condition)
and when target.* is NULL - we insert, when target.* is not null - then we update.
I would hope for two full scans and an hash outer join.
$> merge into t2
2 using t1
3 on (t1.user_id = t2.user_id)
4 when matched then update set username = t1.username, created = t1.created
5 when not matched then insert( user_id, username, created) values ( t1.user_id, t1.username, t1.created );
36 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 791008889
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%
------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2000K| 198M| | 100K
| 1 | MERGE | T2 | | | |
| 2 | VIEW | | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 2000K| 171M| 60M| 100K
| 4 | TABLE ACCESS FULL | T2 | 1000K| 48M| | 30855
| 5 | TABLE ACCESS FULL | T1 | 2000K| 74M| | 61708
------------------------------------------------------------------------
Just like that, if it uses an index - that would indicate a big problem!
MERGE into <target table>
USING
<souce table/view/result of subquery>
ON
<match condition>
WHEN MATCHED THEN
<update clause>
<delete clause>
WHEN NOT MATCHED THEN
<insert clause>
merge into purchased_bricks pb
using (
select 'blue' colour, 'cube' shape, 15.95 price
from dual
) bfs
on ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
insert ( pb.colour, pb.shape, pb.price )
values ( bfs.colour, bfs.shape, bfs.price )
when matched then
update set pb.price = bfs.price;
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
```
```
MERGE INTO employees e
USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
```