Oracle 11g – INSERT, UPDATE, DELETE, MERGE – Interviews

Most of the people familiar with these concepts. I want to discuss these in Advance SQL.

INSERT : is the primary command used in the SQL language to load data. 

Two kinds of Hints available. Basically these are parallel inserts.

insert /*+ append */ into kso.big_emp select * from hr.employees nologging;

insert /*+ append_values */ into dual (dummy) values (‘Y’);  ==> with values

UPDATE:

update skew2 set col1 = col1*1; ==> This is also called mass update. Instead, use INSERT with “APPEND” Hint  to improve performance.

DELETE:

delete from kso.skew2 where col1=1; ==> This is also called mass delete. If the rows are more, it takes more CPU time. If we want to delete all the rows, always use TRUNCATE.

MERGE:It is introduced with 9i. The basic functionality is when match, it will update. When not match it will insert.

MERGE INTO table_name
USING (subquery) ON (subquery.column = table.column)
WHEN MATCHED THEN UPDATE …
WHEN NOT MATCHED THEN INSERT …

Merge has its internal mechanism, it takes more cpu time. So always use insert with “APPEND” to do delete for bulk rows.

About these ads