Merge vs Insert

[kaushik.nandy]

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...]

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...