Oracle two-table data comparison-minus

1 Introduction

In the process of program design, we often encounter comparison between two record sets. For example, the PMS interface of China East Power Grid transfers data that is changed (added, modified, and deleted) within one day. There are multiple implementation methods, such as programming the stored procedure to return the cursor, and comparing two batches of data in the stored procedure.

This article mainly discusses how to use the Oracle minus function to directly compare two record sets.

2 implementation steps

Assume that the two record sets exist as tables, the original table is a, and the comparison table is B.

2.1 determine the incremental differences between the original table and the comparison table

Use the minus function to determine the incremental differences between the original table and the comparison table.

This incremental data contains two parts:

1) original table A has and comparison table B does not;

2) the original table A and comparison table B both exist, but some fields have changed.

2.2 compare the incremental differences between the table and the original table

Use the minus function to compare the incremental differences between the table and the original table.

This incremental data contains two parts:

1) Compare table B and original table;

2) compared to both table B and original table A, some fields have changed.

2.3 obtain the result set

The two incremental differences in SQL statements are used to determine whether the comparison table is "inserted", "modified", or "deleted" relative to the original table.

3. instance Drill

3.1 create a table and insert data.

Create Table A (A1 number (12), A2 varchar2 (50 ));
Create Table B (b1 number (12), B2 varchar2 (50 ));
Insert into a values (1, 'A ');
Insert into a values (2, 'ba ');
Insert into a values (3, 'CA ');
Insert into a values (4, 'da ');
Insert into B values (1, 'A ');
Insert into B values (2, 'bba ');
Insert into B values (3, 'CA ');
Insert into B values (5, 'dda ');
Insert into B values (6, 'eda ');
Commit;

3.2 compare incremental differential data

3.2.1 incremental differences between original table A and Comparison Table B

Select * from a minus select * from B;

The result is as follows:

A1 A2
---------------------------------------------------------------
2 ba
4 da

3.2.2 compare the incremental differences between table B and original Table

Select * from B minus select * From;

The result is as follows:

B1 B2
---------------------------------------------------------------
2 BBA
5 DDA
6 EDA

3.2.3 collection of two incremental differences

This collection contains three types of data:

-- 1. The original table A exists and the comparison table B does not exist. It is a type of deleted data. The number of occurrences is 1.

-- 2. The original table A does not exist, and the comparison table B exists. It is a new type of data. The number of occurrences is 1.

-- 3. Both the original table A and the comparison table B exist. The data belongs to the modification type. The number of occurrences is 2.

Select A1, A2, 1 t from (select * from a minus select * from B) Union
Select b1, b2, 2 t from (select * from B minus select * From );

The result is as follows:

A1 A2 t
-------------------------------------------------------------------------
2 BA 1
2 BBA 2
4 DA 1
5 DDA 2
6 EDA 2

3.3. The result is displayed.

Select A1, sum (T) from
(Select A1, A2, 1 t from (select * from a minus select * from B) Union
Select b1, b2, 2 t from (select * from B minus select * From ))
Group by A1;

The result is as follows:

A1 sum (t)
-----------------------
6 2
2 3
4 1
5 2

In the result, sum (t) is 1 and "delete" data, sum (t) is 2 and add data is sum (t) 3 is the "modified" data.

4. Analysis

4.1 Efficiency Analysis

Serial number

Database Configuration

Oracle version

Original table data volume

Compare table data volume

Field Columns

Time consumed

1

CPU: 2.5 GHz/memory: 2048 m

9i

928335

3608159

19

171.594 s

2

CPU: 2.5 GHz/memory: 2048 m

9i

928335

3608159

10

121.469 s

3

CPU: 2.5 GHz/memory: 2048 m

9i

928335

3608159

5

68.938 s

4

CPU: 2.5 GHz/memory: 2048 m

9i

49933

928335

19

33 s

5

CPU: 2.5 GHz/memory: 2048 m

9i

49933

928335

10

25.968 s

6

CPU: 2.5 GHz/memory: 2048 m

9i

49933

928335

5

11.484 s

7

16 CPU: 3.5 GHz/memory: 64 GB

10g

575283

575283

11

13.812 s

8

16 CPU: 3.5 GHz/memory: 64 GB

10g

109987

109987

40

2.17 s

4.2 Implementation Analysis

In the process of comparing two result sets, reducing the number of fields compared between the original table and the comparison table, as well as the data volume of the original table and the comparison table can improve the efficiency.

5. Summary

This comparison method may not be very effective in terms of execution efficiency, but it can solve the problem of low efficiency requirements. The implementation utilizes the Oracle minus function. This article is intended to give you an understanding of Oracle functions.

 

Address: http://blog.sina.com.cn/s/blog_3ff4e1ad0100tdl2.html