Several ways to delete duplicate records in Oracle

Several ways for Oracle to delete duplicate records [html] several ways for Oracle to delete duplicate records if a file is imported into the database multiple times, duplicate records may be introduced, which methods can be used to delete duplicate records? Reate table tbl_test (SER_NO NUMBER, FST_NM VARCHAR2 (30), deptid number, CMNT VARCHAR2 (30); insert into tbl_test VALUES (1, 'aaaaa', 2004, 'xxx'); insert into tbl_test VALUES (2, 'bbbbbb', 2005, 'yyy'); insert into tbl_test VALUES (1, 'aaaaa', 2004, 'xxx'); insert into tbl_test VALUES (1, 'aaaaa', 2004, 'xxx'); insert into tbl_test VALUES (3, 'ccccc ', 2005, 'zzz'); insert into tbl_test VALUES (2, 'bbbbb ', 2005, 'yyy'); 1. using MIN (rowid) is the most common method, but if the data volume is large, the execution will take a long time to delete from tbl_test where rowid not in (select min (ROWID) FROM tbl_test group by ser_no, fst_nm, deptid, cmnt); 2. using MIN (rowid) & Join is similar to the first delete from tbl_test t WHERE t. rowid not in (select min (B. ROWID) FROM tbl_test B WHERE B. ser_no = t. ser_no AND B. fst_nm = t. fst_nm AND B. deptid = t. deptid AND B. cmnt = t. cmnt); 3. using Subquer Y delete from tbl_test WHERE ser_no IN (SELECT ser_no FROM tbl_test group by ser_no, fst_nm, deptid, cmnt having count (*)> 1) AND divide IN (SELECT fst_nm FROM tbl_test group by ser_no, fst_nm, deptid, cmnt having count (*)> 1) AND deptid IN (SELECT deptid FROM tbl_test group by ser_no, fst_nm, deptid, cmnt having count (*)> 1) AND cmnt IN (SELECT cmnt FROM tbl_test group by ser_no, fst_nm, de Ptid, cmnt having count (*)> 1) and rowid not in (select min (ROWID) FROM tbl_test group by ser_no, fst_nm, deptid, cmnt having count (*)> 1) 4. using Nested Subqueries delete from tbl_test a WHERE (. ser_no,. fst_nm,. deptid,. cmnt) IN (SELECT B. ser_no, B. fst_nm, B. deptid, B. cmnt FROM tbl_test B WHERE. ser_no = B. ser_no AND. fst_nm = B. fst_nm AND. deptid = B. deptid AND. cmnt = B. cmnt AND A. ROWID> B. ROWID); 5. using Analytic Fucntions: This is the most effective method for large tables: delete from tbl_test where rowid in (SELECT rid FROM (select rowid rid, ROW_NUMBER () OVER (partition by ser_no, fst_nm, deptid, cmnt order by rowid) rn FROM tbl_test) WHERE rn <> 1); 6. CREATE-DROP-RENAME is reasonable for resource usage, especially for large tables. However, if rollback is required, a large amount of undo log information will be generated. Create table tbl_test1 nologging as select tbl_test. * FROM tbl_test where rowid in (SELECT rid FROM (select rowid rid, ROW_NUMBER () OVER (partition by ser_no, fst_nm, deptid, cmnt order by rowid) rn FROM tbl_test) WHERE rn = 1); drop table tbl_test; -- drop the original table with lots of duplicate RENAME tbl_test1 TO tbl_test; -- your original table without duplicates.