Oracle Data Matching Merge into____oracle

Objective:
A long time ago, estimated in 2010 years or so in the use of Oralce, there is a need to match the data of the two tables, the two tables of the structure of the same, one is a formal table, a temporary table, the two tables of data is a relatively large amount of hundreds of M. The business requirement is to match the data in the temporary table with the formal table, all fields need one by one matches, and the two tables do not have a primary key, which is a more troublesome and bad thing.

Scene:
1, if the two tables all field values are consistent does not handle;
2, if some of the field inconsistencies are updated;
3. If the data in the formal table does not exist in the temporary table, it needs to be deleted;


To meet the above three functions of the scene, you can use the program such as (java,c,c#) and other implementations, can also use the stored procedure Oracle implementation;
After considering using the program to do, this is certainly achievable, but aside from the tedious data reading, operation of SQL, but also need to match, and efficiency is a problem, and decided to use stored procedures to achieve, previously used if exists to match. Later found that the efficiency is not high, relatively slow, after the subsequent improvement of the introduction of Oracle merge into to achieve;



The specific case code (most of the fields removed) is as follows:

1. Compare the data in the temporary table l_table with the formal table z_table, if the values of each field are not equal, the row data of the temporary table is considered new and inserted into the formal table.

Merge into z_table T1  
  using (  
     select   
         s_system_id,  
         s_port_id,  
         s_system_name  
    where s_system_ NAME = "Guangdong" from  
    l_table  
  ) T2 on  
  (  
  t1. S_port_id=t2. s_port_id and T1. S_system_id=t2. s_system_id and  NVL (t1. S_system_name, ' 1 ') =NVL (T2. S_system_name, ' 1 ') when not  
  matched THEN  
  INSERT (  
   s_system_id,  
   s_port_id,  
   S_system _name  
   )   
   VALUES (  
   t2. s_system_id, T2. S_port_id,t2. S_system_name  
   )  

Note:

1 above code on (NVL (T1). S_system_name, ' 1 ') =NVL (T2. S_system_name, ' 1 '), with the NVL function this place needs special attention, the original test when found that some fields are empty null,null and null can not be used to deal with, so for those null or "" field unified into a string to match , as equals, otherwise there will be no matching problems. 2 in the Query temporary table when added a conditional constraint where s_system_name = "Guangdong"; the previous processing is full volume, query the entire large table, later found that the efficiency is not very perfect; When you add a conditional judgment, you're going to use only a small amount of data to match a formal table at a time. This can reduce the burden of temporary space in the database, more than the circulation of several urban provinces to do, can speed up.

2, delete the redundant data in the official table, the last temporary table and the official table data amount is equal; z_table;
In the previous 1th step, if one of the fields of a row of data is not the same, it will reinsert a row of data into the formal table instead of updating, so the formal table will have extra data that is not exactly equal to the temporary table.

Merge into z_table T1  
  using (  
      Select s_system_id, s_port_id,s_system_name from z_table  
   minus  
      Select S_ system_id, s_port_id,s_system_name from l_table  
  ) T2 on  
  (  
   t1. S_port_id=t2. s_port_id and T1. S_system_id=t2. s_system_id and  NVL (t1. S_system_name, ' 1 ') =NVL (T2. S_system_name, ' 1 ')  
  when  
   matched then  
    update set t1.additionalinfo = ' del '  
    delete WHERE T1.additionalinfo = ' del '

The above SQL means to pick out the difference data in the formal and temporary tables (including the difference between the values of each field), the Oracle's minus function is used to brush the selection, in fact, this is the need to delete the data, this part of the data collection and the formal table to match, If present in the formal table, update the delete identity and delete it.

Note:

Because both tables do not have a primary key, it is not possible to use a unique judgment to delete the delete as the basis for deletion, and the merge into statement tests the following delete must follow the update, this is more tangled, so you can only add an extended field to the formal table AdditionalInfo As a marker for deletion in order to delete the processing.




through the above two steps, the data for the formal and temporary tables has been synchronized.