oracle 兩表資料對比—minus

1 引言

在程式設計的過程中，往往會遇到兩個記錄集的比較。如華東電網PMS介面中實現傳遞一天中變更(新增、修改、刪除)的資料。實現的方式有多種，如編程預存程序返回遊標，在預存程序中對兩批資料進行比較等等。

本文主要討論利用ORACLE的MINUS函數，直接實現兩個記錄集的比較。

2 實現步驟

假設兩個記錄集分別以表的方式存在，原始表為A，產生的比較表為B。

2.1 判斷原始表和比較表的增量差異

利用MINUS函數，判斷原始表與比較表的增量差異。

此增量資料包含兩部分：

1)原始表A有、比較表B沒有;

2)原始表A和比較表B都有，但是某些欄位發生了改變。

2.2 判斷比較表與原始表的增量差異

利用MINUS函數，判斷比較表與原始表的增量差異。

此增量資料包含兩部分：

1)比較表B有、原始表A沒有;

2)比較表B和原始表A都有，但是某些欄位發生了改變。

2.3 得出結果集

利用SQL語句中的對兩種增量差異的處理，實現判別出比較表相對於原始表是進行了“插入”、“修改”、“刪除”的情況。

3 執行個體演練

3.1建立表並插入資料

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進行增量差異資料比較

3.2.1原始表A與比較表B的增量差異

Select * from A minus select * from B;

結果如下：

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

3.2.2比較表B與原始表A的增量差異

Select * from B minus select * from A;

結果如下：

           B1            B2
---------------------------------------------------------------
            2            bba
            5            dda
            6            Eda

3.2.3兩種增量差異的合集

此合集包含3類資料：

--1、原始表A存在、比較表B不存在，屬於刪除類資料，出現次數1

--2、原始表A不存在、比較表B存在，屬於新增類資料，出現次數1

--3、原始表A和比較表B都存在，屬於修改類資料，出現次數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);

結果如下：

           A1                   A2               T
------------- -------------------------------------------------- ----------
            2                   ba                1
            2                   bba               2
            4                   da                1
            5                   dda               2
            6                   Eda               2

3.3得到結果

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 A))
Group by A1;

結果如下：

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

結果中SUM(T)為1的為“刪除”的資料，SUM(T)為2的為“新增”的資料，SUM(T)為3的為“修改”的資料。

4 分析

4.1 效率分析

序號

資料庫配置

Oracle版本

原表資料量

比較表資料量

欄位列數

耗時

1

Cpu:2.5GHz/記憶體:2048M

9i

928335

3608159

19

171.594s

2

Cpu:2.5GHz/記憶體:2048M

9i

928335

3608159

10

121.469s

3

Cpu:2.5GHz/記憶體:2048M

9i

928335

3608159

5

68.938s

4

Cpu:2.5GHz/記憶體:2048M

9i

49933

928335

19

33s

5

Cpu:2.5GHz/記憶體:2048M

9i

49933

928335

10

25.968s

6

Cpu:2.5GHz/記憶體:2048M

9i

49933

928335

5

11.484s

7

16cpu:3.5GHz/記憶體:64G

10g

575283

575283

11

13.812s

8

16cpu:3.5GHz/記憶體:64G

10g

109987

109987

40

2.17s

4.2實現分析

在兩個結果集比較的過程中，減少原始表和比較表比較的欄位數目以及原始表和比較表的資料量都可以提高效率。

5 總結

此比較方法在執行效率上，可能不是非常好，但是能解決效率要求並不太高的問題。在實現上利用了Oracle的minus函數，此文在於引起大家對於Oracle函數的認識。

 

原文地址：http://blog.sina.com.cn/s/blog_3ff4e1ad0100tdl2.html