How Delete duplicate rows from table in oracle

Introduction

http://nimishgarg.blogspot.com/2011/01/oracle-delete-duplicate-rows-from-table.html

In this snippet, i will show how to delete duplicate rows  from oracle.

Lets say out table is

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                                NOT NULL             NUMBER(4)
 ENAME                                                                                 VARCHAR2(10)
 JOB                                                                                        VARCHAR2(9)
 MGR                                                                                      NUMBER(4)
 HIREDATE                                                                            DATE
 SAL                                                                                        NUMBER(7,2)
 COMM                                                                                  NUMBER(7,2)
 DEPTNO                                                                               NUMBER(2)

1. Simple & Easy Way

delete from emp 
where rowid 
not in (select max(rowid) from emp group by empno,ename,job,mgr,hiredate,sal,comm,deptno);

2. Optimized with outer Join

delete from emp where rowid in 
(
 select rid_d from 
 (select rowid rid_d from emp) a,
 (select max(rowid) rid from emp group by empno,ename,job,mgr,hiredate,sal,comm,deptno) b
 where rid_d = rid (+)
 and rid is null
)

3. Alternate way to delete duplicate with analytical function

delete from emp
where rowid in 
( 
 select 
  rid
 from 
 ( 
  select 
   rowid rid,
   row_number() over (partition by empno,ename,job,mgr,hiredate,sal,comm,deptno order by rowid) rn
  from emp
 )
 where rn <> 1 
)

Hope help and thank you for reading.