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