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

Have Something to Say? Post Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s