Oracle: Delete duplicate rows from table

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
)

Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html

Related Posts:
- Oracle - DDL, DML and DCL commands
- ORA-01439: column to be modified must be empty to change datatype
- ORA-02438: Column check constraint cannot reference other columns
- Oracle 11g: Virtual Column