pengpenglin

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

posts - 262, comments - 221, trackbacks - 0

【原】Oracle開發(fā)專題之：刪除重復(fù)記錄

一、測試環(huán)境：

假設(shè)目前我們有一個(gè)表：test，該表的結(jié)構(gòu)如下：

SQL> desc test;

Name Null? Type

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

ID NUMBER

SEQ NUMBER

現(xiàn)在我們向表中插入200W條數(shù)據(jù)，這200W條數(shù)據(jù)中有一半是重復(fù)的。

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;

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

二、使用臨時(shí)表進(jìn)行刪除：

這個(gè)是最簡單的思路了，創(chuàng)建一張臨時(shí)表，將原表中的數(shù)據(jù)拷貝一半過去，再查詢出來。

SQL> set timing on;

SQL>

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

Table created.

Elapsed: 00:00:07.09

SQL>

該方法耗時(shí)7.09秒，測試數(shù)據(jù)庫位于服務(wù)器上。考慮到服務(wù)器和本機(jī)位于同一個(gè)局域網(wǎng)內(nèi)，該時(shí)間如果在真正的生產(chǎn)環(huán)境中應(yīng)該至上延長1倍以上。

三、使用rowid進(jìn)行刪除：

我們知道在Oracle中，rowid是用來唯一表示一條記錄的偽列，任意兩條記錄的rowid都是不同的，即便內(nèi)容看起來一模一樣。所以我們的思路是：使用表的自連接，查找那些內(nèi)容相同但rowid不同的記錄，即為重復(fù)記錄。然后隨意選擇其中一個(gè)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條記錄就是重復(fù)記錄，所以我們可以通過選擇其中rowid較大或較小的記錄，來刪除剩余的記錄。但是這種方法的一個(gè)很大的缺點(diǎn)就是由于采用了“自連接”，對于像我這樣的測試表中有200W條記錄的情況，其自連接后的記錄數(shù)是一個(gè)天文數(shù)字(其實(shí)本人的測試就因?yàn)榈却^久而不得不取消)。

我們換另外一種方法：

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);

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

SQL> DELETE FROM test

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

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

小結(jié)：
在數(shù)據(jù)量不大的情況下，采用根據(jù)rowid或結(jié)合group by分組的方式是很快的，但是在海量數(shù)據(jù)的情況下則反而是方式一最快，因?yàn)槭∪チ俗赃B接、排序、分組的時(shí)間

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

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

新用戶注冊刷新評論列表


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




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