【探索】兩種查詢和刪除重複記錄的方法及其效能比較
這裡我來給出兩種查詢和刪除重複記錄的方法，一種是使用rowid輔助完成的，另外一種是藉助分析函式的力量來完成的。
這兩種方法的執行效率相對其他方法是高效的。即便如此，這兩種方法之間也有著本質上的效能區別，我將透過實驗的方式給大家展示一下這兩種方法，並道出其中的本質差別。
1.建立實驗用表並初始化幾條樣本資料
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輔助查詢和刪除重複記錄的方法
1）查詢重複記錄
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欄位同時重複的內容，可以在上面的子查詢中再新增一個“AND t1.y = t2.y”條件即可。
2）刪除重複記錄
可以簡單的將上面的查詢語句改寫成刪除語句便可完成刪除任務。
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欄位重複的內容已經被刪除了。
sec@ora10g> select * from t;
X Y
---------- --------------------
1 sec
2 Andy01
3 Anna
4 Anna
5 John
3.第二種使用分析函式輔助查詢和刪除重複記錄的方法
1）使用分析函式可以快速的定位重複記錄的位置，下面結果中rn值大於1的行即表示重複行。
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結果作為輔助條件便可得到重複記錄內容
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構造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）第二種方法，可以使用構造中間表t1的方法來完成，這是一種非常高效的去重方法，推薦在具有海量資料的資料庫環境中使用。
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.比較兩種查詢方法的執行計劃，便可得到兩種方法內在的效能差距的出處。
1）第一種使用rowid輔助查詢的執行計劃如下
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）第二種使用分析函式輔助查詢的執行計劃如下
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）透過比較上面兩個執行計劃可以得到如下結論
第一種方法採用2次TABLE ACCESS FULL，第二種方法採用僅一次TABLE ACCESS FULL。
從執行計劃上可以得出使用分析函式的方法更加的高效。
5.小結
在DBA資料庫維護工作中，重複記錄去除問題往往是不可避免的。在具有海量資料的資料庫中去除重複記錄是一件很艱鉅的任務，如果方法選擇不正確，很可能難以完成任務。
我這裡介紹的兩種方法都是相對比較高效的，細節之處請慢慢體會。
完成任務的手段和方法很多，只有將維護時間和對生產資料庫的衝擊較少到最低的方法才是可以接受的正確方法。
Good luck.
-- The End --
這兩種方法的執行效率相對其他方法是高效的。即便如此，這兩種方法之間也有著本質上的效能區別，我將透過實驗的方式給大家展示一下這兩種方法，並道出其中的本質差別。
1.建立實驗用表並初始化幾條樣本資料
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輔助查詢和刪除重複記錄的方法
1）查詢重複記錄
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欄位同時重複的內容，可以在上面的子查詢中再新增一個“AND t1.y = t2.y”條件即可。
2）刪除重複記錄
可以簡單的將上面的查詢語句改寫成刪除語句便可完成刪除任務。
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欄位重複的內容已經被刪除了。
sec@ora10g> select * from t;
X Y
---------- --------------------
1 sec
2 Andy01
3 Anna
4 Anna
5 John
3.第二種使用分析函式輔助查詢和刪除重複記錄的方法
1）使用分析函式可以快速的定位重複記錄的位置，下面結果中rn值大於1的行即表示重複行。
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結果作為輔助條件便可得到重複記錄內容
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構造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）第二種方法，可以使用構造中間表t1的方法來完成，這是一種非常高效的去重方法，推薦在具有海量資料的資料庫環境中使用。
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.比較兩種查詢方法的執行計劃，便可得到兩種方法內在的效能差距的出處。
1）第一種使用rowid輔助查詢的執行計劃如下
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）第二種使用分析函式輔助查詢的執行計劃如下
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）透過比較上面兩個執行計劃可以得到如下結論
第一種方法採用2次TABLE ACCESS FULL，第二種方法採用僅一次TABLE ACCESS FULL。
從執行計劃上可以得出使用分析函式的方法更加的高效。
5.小結
在DBA資料庫維護工作中，重複記錄去除問題往往是不可避免的。在具有海量資料的資料庫中去除重複記錄是一件很艱鉅的任務，如果方法選擇不正確，很可能難以完成任務。
我這裡介紹的兩種方法都是相對比較高效的，細節之處請慢慢體會。
完成任務的手段和方法很多，只有將維護時間和對生產資料庫的衝擊較少到最低的方法才是可以接受的正確方法。
Good luck.
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-620064/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql語句查詢指定重複記錄和刪除重複記錄僅保留一條【親測可以】MySql
- MariaDB刪除重複記錄效能測試
- 刪除重複id的記錄
- 使用SQL語句去掉重複記錄的兩種方法SQL
- sql刪除重複記錄只保留一條SQL
- MySQL資料庫中刪除重複記錄的方法總結[推薦]MySql資料庫
- MySQL刪除重複記錄並保留第一條MySql
- MySql避免重複插入記錄的幾種方法MySql
- 探討一個比較複雜的查詢
- 查詢/刪除重複的資料(單個欄位和多個欄位條件)
- 幾種刪除Linux目錄的方法Linux
- 查詢陣列裡資料刪除和增加的方法陣列
- Oracle如何刪除表中重複記錄保留第一條Oracle
- sql根據多個欄位查詢重複記錄SQL
- 查詢字串中第一個非重複字元的3種方法字串字元
- Linux 中刪除目錄的多種方法Linux
- VSCode刪除重複的空行VSCode
- 同一張表的兩個欄位比較查詢
- PolarDB-X 1.0和RDS效能對比之複雜查詢
- mysql刪除查詢MySql
- Win10如何刪除字型_win10刪除字型的兩種方法Win10
- 比較兩個List物件，找出新增、刪除、更改的條目物件
- mysql 刪除重複項MySql
- 刪除oracle重複值Oracle
- JavaScript 刪除重複字元JavaScript字元
- 刪除重複資料
- MySQL防止delete命令刪除資料的兩種方法MySqldelete
- 原生JS去重（一）--兩種方法去掉重複字元JS字元
- mysql timestamp比較查詢MySql
- 【常用方法推薦】如何刪除MySQL的重複資料?MySql
- BST查詢結構與折半查詢方法的實現與實驗比較
- 二叉查詢樹的插入刪除查詢
- 四個id 生成器效能比較記錄
- iOS開發之tableView左滑刪除的兩種方法iOSView
- mongodb刪除重複資料MongoDB
- JavaScript 刪除字串重複字元JavaScript字串字元
- Excel查詢重複項Excel
- 查詢Set中重複的元素
- MySQL 查詢重複的資料MySql