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 /
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
