posts - 262,  comments - 221,  trackbacks - 0
          一、測(cè)試環(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ù)記錄。下面來(lái)看一下各種方法的使用及效率區(qū)別

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

          這個(gè)是最簡(jiǎn)單的思路了,創(chuàng)建一張臨時(shí)表,將原表中的數(shù)據(jù)拷貝一半過(guò)去,再查詢(xún)出來(lái)。
          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秒,測(cè)試數(shù)據(jù)庫(kù)位于服務(wù)器上。考慮到服務(wù)器和本機(jī)位于同一個(gè)局域網(wǎng)內(nèi),該時(shí)間如果在真正的生產(chǎn)環(huán)境中應(yīng)該至上延長(zhǎng)1倍以上。

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

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

          我們來(lái)看一下其中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是不同的,說(shuō)明這2條記錄就是重復(fù)記錄,所以我們可以通過(guò)選擇其中rowid較大或較小的記錄,來(lái)刪除剩余的記錄。但是這種方法的一個(gè)很大的缺點(diǎn)就是由于采用了“自連接”,對(duì)于像我這樣的測(cè)試表中有200W條記錄的情況,其自連接后的記錄數(shù)是一個(gè)天文數(shù)字(其實(shí)本人的測(cè)試就因?yàn)榈却^(guò)久而不得不取消)。

          我們換另外一種方法:
          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í)踐證明,這種方法對(duì)大量數(shù)據(jù)的情況,效率依然是很低的。結(jié)果如同上一種方法。假如我們?cè)俳Y(jié)合group by呢?
          SQL> DELETE FROM test
            
          2   WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM test GROUP BY id, seq);

          效果如同前面兩個(gè)方法一樣,大量的連接、排序、分組讓依靠rowid來(lái)刪除重復(fù)記錄變得很耗時(shí),反而是采用方法1的情況下速度很快(本人測(cè)試了2次,都是連接測(cè)試服務(wù)器進(jìn)行測(cè)試,第一次用時(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 閱讀(1654) 評(píng)論(0)  編輯  收藏 所屬分類(lèi): Oracle 開(kāi)發(fā)
          <2008年6月>
          25262728293031
          1234567
          891011121314
          15161718192021
          22232425262728
          293012345

          常用鏈接

          留言簿(21)

          隨筆分類(lèi)

          隨筆檔案

          BlogJava熱點(diǎn)博客

          好友博客

          搜索

          •  

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          主站蜘蛛池模板: 天全县| 四子王旗| 重庆市| 金门县| 平阳县| 新巴尔虎左旗| 平罗县| 开原市| 天门市| 宣威市| 宁城县| 高平市| 诏安县| 农安县| 延吉市| 昌平区| 民乐县| 黔东| 壶关县| 中宁县| 忻城县| 蕲春县| 大同县| 白银市| 焦作市| 台安县| 喀什市| 固镇县| 仁怀市| 武安市| 息烽县| 岳普湖县| 衡南县| 嘉黎县| SHOW| 苏尼特右旗| 永仁县| 龙里县| 祥云县| 凌云县| 合川市|