溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

【探索】兩種查詢和刪除重復(fù)記錄的方法及其性能比較

發(fā)布時間：2020-08-12 19:39:47 來源：ITPUB博客 閱讀：118 作者：chuyuanxiang 欄目：建站服務(wù)器
這里我來給出兩種查詢和刪除重復(fù)記錄的方法，一種是使用rowid輔助完成的，另外一種是借助分析函數(shù)的力量來完成的。
這兩種方法的執(zhí)行效率相對其他方法是高效的。即便如此，這兩種方法之間也有著本質(zhì)上的性能區(qū)別，我將通過實驗的方式給大家展示一下這兩種方法，并道出其中的本質(zhì)差別。

1.創(chuàng)建實驗用表并初始化幾條樣本數(shù)據(jù)
sec@ora10g> create table t (x number, y varchar2(10));
sec@ora10g> insert into t values (1, 'sec');
sec@ora10g> insert into t values (2, 'Andy01');
sec@ora10g> insert into t values (2, 'Andy02');
sec@ora10g> insert into t values (3, 'Anna');
sec@ora10g> insert into t values (4, 'Anna');
sec@ora10g> insert into t values (5, 'John');
sec@ora10g> commit;
sec@secooler> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

sec@ora10g> select * from t;

         X Y
---------- --------------------
         1 sec
         2 Andy01
         2 Andy02
         3 Anna
         4 Anna
         5 John

6 rows selected.

2.第一種使用rowid輔助查詢和刪除重復(fù)記錄的方法
1）查詢重復(fù)記錄
sec@ora10g> SELECT *
  2    FROM t t1
  3   WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
  4                        FROM t t2
  5                       WHERE t1.x = t2.x)
  6  /

         X Y
---------- --------------------
         2 Andy02

BTW：如果想要查詢x和y字段同時重復(fù)的內(nèi)容，可以在上面的子查詢中再添加一個“AND t1.y = t2.y”條件即可。

2）刪除重復(fù)記錄
可以簡單的將上面的查詢語句改寫成刪除語句便可完成刪除任務(wù)。
sec@ora10g> DELETE FROM t t1
  2        WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
  3                             FROM t t2
  4                            WHERE t1.x = t2.x)
  5  /

1 row deleted.

可以看到，此時x字段重復(fù)的內(nèi)容已經(jīng)被刪除了。
sec@ora10g> select * from t;

         X Y
---------- --------------------
         1 sec
         2 Andy01
         3 Anna
         4 Anna
         5 John

3.第二種使用分析函數(shù)輔助查詢和刪除重復(fù)記錄的方法
1）使用分析函數(shù)可以快速的定位重復(fù)記錄的位置，下面結(jié)果中rn值大于1的行即表示重復(fù)行。
sec@ora10g> SELECT t1.x,
  2         t1.y,
  3         ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  4    FROM t t1
  5  /

         X Y                            RN
---------- -------------------- ----------
         1 sec                           1
         2 Andy01                        1
         2 Andy02                        2
         3 Anna                          1
         4 Anna                          1
         5 John                          1

6 rows selected.

2）進一步使用上面的rn結(jié)果作為輔助條件便可得到重復(fù)記錄內(nèi)容
sec@ora10g> SELECT t2.x, t2.y
  2    FROM (SELECT t1.x,
  3                 t1.y,
  4                 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  5            FROM t t1) t2
  6   WHERE t2.rn > 1
  7  /

         X Y
---------- --------------------
         2 Andy02

3）刪除方法
（1）第一種方法是利用rowid構(gòu)造delete語句來完成刪除，這種方法效率較低。
sec@ora10g> DELETE FROM t WHERE ROWID IN (
  2    SELECT rowid
  3      FROM (SELECT t1.x,
  4                   t1.y,
  5                   ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  6              FROM t t1) t2
  7     WHERE t2.rn > 1
  8  )
  9  /

1 row deleted.

（2）第二種方法，可以使用構(gòu)造中間表t1的方法來完成，這是一種非常高效的去重方法，推薦在具有海量數(shù)據(jù)的數(shù)據(jù)庫環(huán)境中使用。
sec@ora10g> create table t1 as
  2  SELECT t2.x, t2.y
  3    FROM (SELECT t1.x,
  4                 t1.y,
  5                 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  6            FROM t t1) t2
  7   WHERE t2.rn = 1
  8  /

Table created.

sec@ora10g> drop table t;

Table dropped.

sec@ora10g> alter table t1 rename to t;

Table altered.

sec@ora10g> select * from t;

         X Y
---------- --------------------
         1 sec
         2 Andy01
         3 Anna
         4 Anna
         5 John

4.比較兩種查詢方法的執(zhí)行計劃，便可得到兩種方法內(nèi)在的性能差距的出處。
1）第一種使用rowid輔助查詢的執(zhí)行計劃如下
sec@ora10g> set autot trace exp
sec@ora10g> SELECT *
  2    FROM t t1
  3   WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
  4                        FROM t t2
  5                       WHERE t1.x = t2.x)
  6  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3924487551

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |    55 |     6   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | T    |     6 |    66 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  4 |    TABLE ACCESS FULL| T    |     1 |    11 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1".ROWID<> (SELECT MIN("T2".ROWID) FROM "T" "T2" WHERE
              "T2"."X"=:B1))
   4 - filter("T2"."X"=:B1)

2）第二種使用分析函數(shù)輔助查詢的執(zhí)行計劃如下
sec@ora10g> SELECT t1.x,
  2         t1.y,
  3         ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  4    FROM t t1
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2335850315

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     6 |    66 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |     6 |    66 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     6 |    66 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

3）通過比較上面兩個執(zhí)行計劃可以得到如下結(jié)論
第一種方法采用2次TABLE ACCESS FULL，第二種方法采用僅一次TABLE ACCESS FULL。
從執(zhí)行計劃上可以得出使用分析函數(shù)的方法更加的高效。

5.小結(jié)
在DBA數(shù)據(jù)庫維護工作中，重復(fù)記錄去除問題往往是不可避免的。在具有海量數(shù)據(jù)的數(shù)據(jù)庫中去除重復(fù)記錄是一件很艱巨的任務(wù)，如果方法選擇不正確，很可能難以完成任務(wù)。
我這里介紹的兩種方法都是相對比較高效的，細(xì)節(jié)之處請慢慢體會。

完成任務(wù)的手段和方法很多，只有將維護時間和對生產(chǎn)數(shù)據(jù)庫的沖擊較少到最低的方法才是可以接受的正確方法。

Good luck.

-- The End --
向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手