Toad World® Forums

No merge. I'm not crazy

#1

Hello everyone,

MERGE INTO fntable f
USING cutable c
ON (f.id = c.id)
WHEN MATCHED THEN
UPDATE SET
f.in1 = c.in2,
f.in2 = c.in2,
f.in3 = c.in3,
f.in4 = c.in4,
f.in5 = c.in5,
f.in6 = c.in6,
f.in7 = c.in7,
f.in8 = c.in8,
f.ms = c.ms
where ms = 201905
and f.id=c.id
WHEN NOT MATCHED THEN
INSERT (f.in1, f.in2, f.in3, f.in4, f.in5, f.in6, f.in7, f.in8, f.ms)
VALUES (c.in1, c.in2, c.in3, c.in4, c.in5, c.in6, c.in7, c.in8, c.ms);

no merge
It seems I'm crazy but I'm not crazy
I need the update without using the merge
I also need the insert but without occupying the merge sentence
It's possible?

The following does not work

UPDATE fntable
SET
f.in1 = c.in2,
f.in2 = c.in2,
f.in3 = c.in3,
f.in4 = c.in4,
f.in5 = c.in5,
f.in6 = c.in6,
f.in7 = c.in7,
f.in8 = c.in8,
f.ms = c.ms
where ms = 201905
and f.id=c.id

Could you help me please?
Thanks a lot

#2

Hi antoine_mc1,

If I understand you correctly, you don't (or cannot) use the MERGE statement and you need two separate statements, one to UPDATE rows in the 'master' table, using data in the 'updates' table, and another to INSERT new rows into the 'master' table using data in the 'updates' table.

Here's a small example. You should note that the corresponding key fields in each table must return a single row, or it will cause no end of problems. :frowning:

Interestingly, the following gave me these timings:

  • Merge: 16 milliSeconds.
  • Insert: 15 milliSeconds.
  • Update: 62 milliSeconds.

It may be a bit more efficient to actually use the MERGE statements.

-- Create tables
create table master(k number, a number, b number);
alter table master add constraint pk_master primary key (k);

create table updates(k number, a_new number, b_new number);
alter table updates add constraint pk_updates primary key (k);


-- Load Master data
insert into master values (1,0,0);
insert into master values (2,0,0);
insert into master values (3,0,0);
insert into master values (4,0,0);
insert into master values (5,0,0);
insert into master values (6,0,0);
insert into master values (7,0,0);
insert into master values (8,0,0);
insert into master values (9,0,0);
insert into master values (10,0,0);


-- Load data to use for updates
insert into updates values (4,1,1);
insert into updates values (8,1,1);
insert into updates values (1,1,1);
insert into updates values (10,1,1);


-- Load data to use for inserts
insert into updates values (666,1,1);
insert into updates values (616,1,1);

commit;

Now for the tests:

-- Update
UPDATE master 
SET (a, b) = (SELECT a_new, b_new
                FROM updates
               WHERE master.k = updates.k)
WHERE EXISTS (SELECT 1
               FROM updates
              WHERE master.k = updates.k);
     
-- Check results
select * from master;

commit;

That test gave the following explain plan:

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |            |     4 |   208 |    19  (43)| 00:00:01 |
|   1 |  UPDATE                      | MASTER     |       |       |            |          |
|   2 |   NESTED LOOPS               |            |     4 |   208 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | MASTER     |    10 |   390 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_UPDATES |     1 |    13 |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| UPDATES    |     1 |    39 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_UPDATES |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("MASTER"."K"="UPDATES"."K")
   6 - access("UPDATES"."K"=:B1)
-- Insert
insert into master
    SELECT k, a_new, b_new
      FROM updates
WHERE not EXISTS (SELECT 1
                    FROM master
                   WHERE master.k = updates.k);

-- Check results
select * from master;

commit;

The test above, gave the following explain plan:

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |           |     4 |   208 |     3   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | MASTER    |       |       |            |          |
|   2 |   NESTED LOOPS ANTI      |           |     4 |   208 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | UPDATES   |     4 |   156 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN     | PK_MASTER |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("MASTER"."K"="UPDATES"."K")

And finally, the MERGE that corresponds to the two tests above:

merge into master using updates 
    on (master.k = updates.k)
when matched then
    update set
        a = a_new,
        b = b_new
when not matched then
    insert (k, a, b)
    values (updates.k, a_new, b_new);
    
-- Check results
select * from master;
commit;    

This gives the explain plan below:

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |         |     4 |   312 |     6   (0)| 00:00:01 |
|   1 |  MERGE               | MASTER  |       |       |            |          |
|   2 |   VIEW               |         |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |         |     4 |   360 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| UPDATES |     4 |   156 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| MASTER  |    10 |   510 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("MASTER"."K"(+)="UPDATES"."K")

HTH

Cheers,
Norm. [TeamT]