Shulou(Shulou.com)06/01 Report--

Detailed explanation of an example of oracle data matching merge into

Foreword:

A long time ago, it was estimated that Oralce was being used around 2010. At that time, there was a need to match the data of the two tables. The data structure of the two tables is the same, one is the formal table, the other is the temporary table, and the data volume of the two tables is relatively large by a few hundred megabytes. The business requirement is to match the data in the temporary table with the formal table, all the fields need to be matched one by one, and the two tables do not have a primary key, which is a troublesome and bad thing.

Scene:

1. If all the field values of the two tables are the same, they will not be processed

2. Update if some fields are inconsistent

3. If the data in the official table does not exist in the temporary table, it needs to be deleted

Meet the three functions of the above scenario, which can be implemented by various programs such as (java,C,C#), or by using stored procedure oracle.

After considering it, it is certainly possible to do it with a program, but apart from the tedious data reading and sql operation, matching is also needed, and efficiency is also a problem; we decided to use stored procedures to implement it, and previously used if exists to match. Later, it is found that the efficiency is not high, and it is relatively slow; after the follow-up improvement, Oracle merge into is introduced to implement

The specific case code (most of the fields have been deleted) is as follows:

1. Comparing 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 of data in the temporary table is considered to be new, and then 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.Systematical name) = 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.Systematical ID authoring t2.Systematical name)

Note:

1) the above code on (nvl (t1.SystemechanNAMEprecinct power1') = nvl (t2.SystemSYSTEMnamNAMErecovery1'). We need to pay special attention to the nvl function. When we first tested, we found that some fields were empty null,null and null could not be treated as equals, so those fields that are null or "" can be converted into a string to match as equals. Otherwise, there will be a problem of mismatch.

2) add a conditional constraint where S_SYSTEM_NAME = "Guangdong" when querying the temporary table; the previous processing is full, query the entire large table, and later found that the efficiency is not very perfect; after adding the condition judgment, only a small amount of data is used to match with the official table each time, so that the burden of database temporary space can be reduced, and several cities and provinces can be cycled to do it respectively, which can speed up the speed.

2. Delete the redundant data in the Z_TABLE in the formal table. Finally, the amount of data in the temporary table is equal to that in the formal table.

In the previous step 1, if a field of a row of data is different, a row of data will be re-inserted into the formal table instead of updated, so there will be some extra data in the formal table, that is, data that is not exactly equal to the temporary table.

Merge into Z_TABLE T1 using (select S_SYSTEM_ID, slotted name from Z_TABLE minus select S_SYSTEM_ID, Sound Port ID) T2 on (t1.S_PORT_ID=t2.S_PORT_ID and t1.S_SYSTEM_ID=t2.S_SYSTEM_ID and nvl (t1.Systematical name from L_TABLE) = nvl (t2.S_SYSTEM_NAME) '1') when matched then update set t1.additionalInfo =' del' delete WHERE t1.additionalInfo = 'del'

The above sql means to select the difference data between the official table and the temporary table (including the differences in the values of each field). Here, the minus function of Oracle is used to select the data. In fact, this part is the data that needs to be deleted. Match this part of the data collection with the official table, and update the deletion ID if it exists in the formal table.

Note:

Because both tables do not have a primary key, a unique judgment cannot be used to delete as a basis for deletion, and the statement of merge into tests that delete must be followed by update, which is quite tangled, so you can only add an extended field additionalInfo to the official table as a deletion mark to facilitate deletion processing.

Through the above two steps, the data of formal table and temporary table have been completed synchronously!

If you have any questions, please leave a message or go to the community to exchange and discuss, thank you for reading, hope to help you, thank you for your support!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.