This forum is currently locked. You can't register or post questions at this time. (read more)

Merge vs Insert

All posts relating to Oracle database administration.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron