Oracle Two-table data comparison---minus

1 Introduction

In the process of programming, you will often encounter two sets of records compared. such as the East China power grid PMS interface to implement the transfer of changes (new, modified, deleted) in the day of data. There are many ways to implement it, such as a programmatic stored procedure that returns a cursor, compares two batches of data in a stored procedure, and so on.

This paper mainly discusses the use of Oracle's minus function to directly realize the comparison of two sets of records.

2 Implementation Steps

Suppose that two recordsets exist as tables, the original table is a, and the resulting comparison table is B.

2.1 Determining Delta differences between the original table and the comparison table

The increment difference between the original table and the comparison table is judged by using the minus function.

This incremental data contains two parts:

1) The original table A has, the comparison table B is not;

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

2.2 Judging the delta difference between the comparison table and the original table

The increment difference between the comparison table and the original table is judged by using the minus function.

This incremental data contains two parts:

1) Comparison Table B has, the original table a did not;

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

2.3 Draw the result set

By using the processing of two increment differences in SQL statements, it is realized that the comparison table is "inserted", "Modified" and "deleted" in relation to the original table.

3 Example Walkthrough

3.1 Creating tables and inserting data

Create table A (A1 number, A2 varchar2 (50));
Create table B (B1 number, 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 Comparison of incremental difference data

3.2.1 Delta difference between original table A and comparison table B

SELECT * from A minus select * from B;

The results are as follows:

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

3.2.2 Comparison of delta differences between table B and original Table A

SELECT * from B minus select * from A;

The results are as follows:

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

3.2. Collection of 32 incremental differences

This collection contains 3 types of data:

--1, original Table A exists, Comparison table B does not exist, belongs to delete class data, occurrences 1

--2, the original table A does not exist, the comparison table B exists, belongs to the new class data, the number of occurrences 1

--3, original Table A and comparison table B all exist, belong to the modified class data, the number of occurrences 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 A);

The results are as follows:

A1 A2 T
------------- ------------------------------------------------------------
2 BA 1
2 BBA 2
4 Da 1
5 DDA 2
6 Eda 2

3.3 Getting Results

Select a1,sum (t) from
(select a1,a2,1 t from (SELECT * to A minus select * from B) union
Select b1,b2,2 t from (SELECT * from B minus select * froma))
Group by A1;

The results are as follows:

A1 SUM (T)
-----------------------
6 2
2 3
4 1
5 2

In the result, sum (t) is 1 for "deleted" Data, sum (t) is 2 for "new" data, SUM (t) is 3 for "modified" data.

4 analysis

4.1 Efficiency analysis

Serial number

Database configuration

Oracle version

Original table Data Volume

Compare table Data Volumes

Number of field columns

Take

1

cpu:2.5ghz/Memory: 2048M

9i

928335

3608159

19

171.594s

2

cpu:2.5ghz/Memory: 2048M

9i

928335

3608159

10

121.469s

3

cpu:2.5ghz/Memory: 2048M

9i

928335

3608159

5

68.938s

4

cpu:2.5ghz/Memory: 2048M

9i

49933

928335

19

33s

5

cpu:2.5ghz/Memory: 2048M

9i

49933

928335

10

25.968s

6

cpu:2.5ghz/Memory: 2048M

9i

49933

928335

5

11.484s

7

16cpu:3.5ghz/Memory: 64G

10g

575283

575283

11

13.812s

8

16cpu:3.5ghz/Memory: 64G

10g

109987

109987

40

2.17s

4.2 Implementation Analysis

In the process of comparing two result sets, reducing the number of fields that are compared between the original table and the comparison table and the amount of data for both the original table and the comparison table can improve efficiency.

5 Summary

This comparison method may not be very good at execution efficiency, but it can solve the problem that the efficiency requirement is not too high. Using Oracle's minus function in implementation, this article is about understanding Oracle functions.

Oracle Two-table data comparison---minus