oracle 复制重复数据,oracle 删除重复数据的几种方法

oracle 删除重复数据的几种方法

create table test_0210(id number,name varchar2(32),age number);

insert into test_0210 values(1,'abc',32);

insert into test_0210 values(2,'def',33);

insert into test_0210 values(3,'def',45);

commit;

SQL> select * from test_0210;

ID NAME AGE

---------- -------------------------------- ----------

1 abc 32

2 def 33

3 def 45

1.使用rowid 效率高 直接定位到数据块地址,根据需要取max rowid 或 min rowid 只适合删除少量重复数据

SQL> delete from test_0210 where rowid not in (select max(rowid) from test_0210 group by name);

1 row deleted

SQL> select * from test_0210;

ID NAME AGE

---------- -------------------------------- ----------

1 abc 32

3 def 45

2.利用ID 只适合删除少量重复数据

SQL> delete from test_0210 where id not in (select max(id) from test_0210 group by name);

1 row deleted

SQL> select * from test_0210;

ID NAME AGE

---------- -------------------------------- ----------

1 abc 32

3 def 45

3.创建临时表 ,这种方法适合删除大量重复数据

SQL> create table test_temp as select * from test_0210 where id in (select max(id) from test_0210 group by name)

2 ;

Table created

SQL> truncate table test_0210;

Table truncated

SQL> insert into test_0210 select * from test_temp;

2 rows inserted

SQL> commit;

Commit complete

SQL> select * from test_0210;

ID NAME AGE

---------- -------------------------------- ----------

1 abc 32

3 def 45

4:也是创建临时表 这里用的是分析函数

SQL> create table test_temp as select id,name,age from (select row_number() over(partition by name order by id) rn ,id,name,age

2 from test_0210) where rn=1;

Table created

SQL> truncate table test_0210;

Table truncated

SQL> insert into test_0210 select * from test_temp;

2 rows inserted

SQL> commit;

Commit complete

SQL> select * from test_0210;

ID NAME AGE

---------- -------------------------------- ----------

1 abc 32

2 def 33

总结删除重复数据的方法很多,具体看需求,而选择最符合自己的sql

也可通过表改名的方式 这样最快 省去插入数据时间 SQL> create table test_temp as select * from test_0210 where id in (select max(id) from test_0210 group by name) ; alter table test rename to test_old; alter table test_temp rename to test