Merge vs Insert

All posts relating to Oracle database administration.

Moderator: Tim...

Merge vs Insert

Postby kaushik.nandy » Wed Nov 07, 2012 11:07 am

In these two separate statements
Insert into Tablename1 values ( col1 , col2 );
and
merge into tablename1 a
USING tablename2 b
ON (condition)
WHEN NOT MATCHED THEN
Insert into (col1, col2 ) values (col1,col2);


the only thing that I feel differentiates the above two statements is the WHEN NOT MATCHED statement.
Does oracle internally perform any additional step when it encounters the WHEN NOT MATCHED to do an insert in the MERGE statement?
I mean to say, it does check if the record/s does exist in the table . so it must be doing an additional search in tablename1.
If not then both the statements does the same thing. ..
Please correct me if I am thinking wrong..


Regards,
Kaushik Nandy
kaushik.nandy
Member
 
Posts: 43
Joined: Sun Oct 31, 2010 8:12 am

Re: Merge vs Insert

Postby Tim... » Wed Nov 07, 2012 12:10 pm

Hi.

The merge definitely check for presence of the data. The operation will only be performed if the data is not present. The insert is different because it will always attempt the insert, but that attempt may fail because of duplication errors (assuming you have the appropriate integrity constraints).

You can see the difference in a little test.

Setup:
Code: Select all
CREATE TABLE t1 (
  id NUMBER,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

INSERT INTO t1 VALUES (1);
COMMIT;


Test:
Code: Select all
SQL> MERGE INTO t1 a
USING (select 1 AS id from dual) b
ON (a.id = b.id)
WHEN NOT MATCHED THEN
  INSERT (id)
  VALUES (b.id);

0 rows merged.

SQL>
 
INSERT INTO t1 VALUES (1);

SQL> INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_PK) violated


SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron