Summary of merge into usage in Oracle (reproduced)

Summary of merge into usage in Oracle (Source: http://www.cnblogs.com/dongsheng/p/4384754.html)

causes :

Some time ago, because it involves a table of big data operations, to make additions and deletions at the same time, I and master think a lot of optimization methods, the results are unsatisfactory. Just started to use the original algorithm, first update the existing records, and then insert the other records to meet the requirements, and finally delete the extra records, but a small amount of data can also, 10W data is not, the foreground of the time-out is 60s, far from meeting the requirements. And then find a way to split the task, according to each record serial number of the tail character split, with multiple threads at the same time, has been split into 10 tasks (tail characters are 0, 1, 2, 3 ... 9), with 10 threads at the same time to execute, but the time is still very slow, up to processing 13W data around, 50W data is unbearable. Finally helpless had to the previous optimization all shovel off, instead of merge into operation, and then do some optimization, the single-threaded 50W data time-consuming shortened to more than 30 seconds, hereby, the usage of merge into is summarized as follows: (http://blog.csdn.net/yuzhic/ article/details/1896878)

There is a table T, there are two fields A, B, we want to do insert/update in table T, and if the condition is met, update the value of B in T, or insert a record in T. In Microsoft's SQL syntax, a simple sentence can be judged, and the syntax in SQL Server is as follows:

if exists (select 1 from T where t.a= ' 1001 ')    update T set t.b=2 where t.a= ' 1001 ' Else     

In Oracle, to achieve the same functionality, merge into is implemented (the functionality introduced by Oracle 9i) with the following syntax:

MERGE into table_name alias1 USING (table|view|sub_query) alias2on (join condition) while matched then     UPDATE Table_nam E     SET col1 = col_val1,            col2 = Col_val2 when not matched then     

Strictly speaking,"in a merge statement that has both the INSERT and update syntax, the total number of insert/update records is the number of records ALIAS2 in the using statement." so, to implement the above function, you can write this:

MERGE into T-t1using (SELECT ' 1001 ' as a,2 as b from dual) T2on (t1.a=t2.a) when matched then    UPDATE SET t1.b = t2.bw HEN not matched then     INSERT (A, B) VALUES (t2.a,t2.b);

Summary of merge into usage in Oracle (reproduced)