pengpenglin

人，必須選擇一種生活方式并有勇氣堅持下去

posts - 262, comments - 221, trackbacks - 0

【原】Oracle開發專題之：刪除重復記錄

一、測試環境：

假設目前我們有一個表：test，該表的結構如下：

SQL> desc test;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER

SEQ NUMBER

現在我們向表中插入200W條數據，這200W條數據中有一半是重復的。

create or replace procedure gen_duplicated_records as

i number;

j number;

begin

for i in 1 .. 2 loop

for j in 1 .. 1000000 loop

insert into test values (j, j + 10);

end loop;

commit;

end loop;

end;

我們的最終目的就是剔除這一半的重復記錄。下面來看一下各種方法的使用及效率區別

二、使用臨時表進行刪除：

這個是最簡單的思路了，創建一張臨時表，將原表中的數據拷貝一半過去，再查詢出來。

SQL> set timing on;

SQL>

SQL> create table test_2 as select distinct * from test;

Table created.

Elapsed: 00:00:07.09

SQL>

該方法耗時7.09秒，測試數據庫位于服務器上。考慮到服務器和本機位于同一個局域網內，該時間如果在真正的生產環境中應該至上延長1倍以上。

三、使用rowid進行刪除：

我們知道在Oracle中，rowid是用來唯一表示一條記錄的偽列，任意兩條記錄的rowid都是不同的，即便內容看起來一模一樣。所以我們的思路是：使用表的自連接，查找那些內容相同但rowid不同的記錄，即為重復記錄。然后隨意選擇其中一個rowid代表的記錄，刪除另一條記錄。

我們來看一下其中id=1的記錄在自連接后的情況：

SQL> select a.*, a.rowid, b.*, b.rowid from test a, test b where a.id = b.id and a.seq = b.seq and a

.id = 1;

ID SEQ ROWID ID SEQ ROWID

---------- ---------- ------------------ ---------- ---------- ------------------

1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAAKAAA

1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAAKAAA

1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAgQAGX

1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAgQAGX

Elapsed: 00:00:02.08

SQL>

我們看到自連接后的4條記錄中有2條的rowid是不同的，說明這2條記錄就是重復記錄，所以我們可以通過選擇其中rowid較大或較小的記錄，來刪除剩余的記錄。但是這種方法的一個很大的缺點就是由于采用了“自連接”，對于像我這樣的測試表中有200W條記錄的情況，其自連接后的記錄數是一個天文數字(其實本人的測試就因為等待過久而不得不取消)。

我們換另外一種方法：

DELETE FROM test t1

WHERE t1.ROWID NOT IN (

SELECT MAX(t2.rowid)

FROM test t2

WHERE t1.id = t2.id AND t1.seq = t2.seq);

實踐證明，這種方法對大量數據的情況，效率依然是很低的。結果如同上一種方法。假如我們再結合group by呢？

SQL> DELETE FROM test

2 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM test GROUP BY id, seq);

效果如同前面兩個方法一樣，大量的連接、排序、分組讓依靠rowid來刪除重復記錄變得很耗時，反而是采用方法1的情況下速度很快(本人測試了2次，都是連接測試服務器進行測試，第一次用時7.09秒，第二次用時14.656秒)。

小結：
在數據量不大的情況下，采用根據rowid或結合group by分組的方式是很快的，但是在海量數據的情況下則反而是方式一最快，因為省去了自連接、排序、分組的時間

-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。

posted on 2008-06-18 11:04 Paul Lin 閱讀(1651) 評論(0) 編輯收藏所屬分類: Oracle 開發

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: 【原】Oracle開發專題之：時間運算2(日期截取及四舍五入) 【原】Oracle開發專題之：時間運算【原】Oracle開發專題之：分析函數總結【原】Oracle開發專題之：報表函數【原】Oracle開發專題之：窗口函數【原】Oracle應用專題之：分析函數3(Top/Bottom N、First/Last、NTile) 【原】Oracle開發專題之：分析函數2(Rank, Dense_rank, row_number) 【原】Oracle開發專題之：分析函數(OVER) 【原】Oracle開發專題之：刪除重復記錄【原】Oracle開發專題之：行列轉換