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 skew2 set col1 = col1*1; ==> This is also called mass update. Instead, use INSERT with “APPEND” Hint to improve performance.
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.
- Top 10 Oracle SQL tuning tips (slideshare.net)
- Sql Commands (ocpbhola.wordpress.com)
- Synchronize 2 tables with a SINGLE SQL statement in Oracle, by Zahar Hilkevich (sqlpatterns.wordpress.com)