表中排重的常规做法有:

1 distinct 受限于表的数据量的大小和机器配置

2 rowid 方法.

delete from table where rowid in (select a.rowid from table a,table b where

a.col1=b.col1 and a.col2=b.col2 ...and a.rowid

这个相对来说是效率比较高的.

最近看书,发现9i及以后的分析函数,效率更高.

使用如下的例子进行测试.

sms_sys_param_test 表里面有260万,找出不重复的只用12s(机器10.40.95.105)

sms_sys_param_test的数据里面不重复的是91条,来源于sms_sys_param.
采用 insert into sms_sys_param_test select * from sms_sys_param_test反复复制.没有建任何所引.

select * from
(select sms_sys_param_test.*,row_number() over(partition by PARAID order by rowid) rn from sms_sys_param_test)
where rn=1;

如果重复的数据很多,可以采用如下方式排重.
create table newtable_name
as select * from
(select sms_sys_param_test.*,row_number() over(partition by PARAID order by rowid) rn from sms_sys_param_test)
where rn=1;

truncate table sms_sys_param_test;

insert into sms_sys_param_test select .... from newtable_name;

drop table newtable_name;

如果很少,可以把上述的rn=1改为rn>1 .

delete from sms_sys_param_test where rowid in (
select rowid from (select rowid,sms_sys_param_test.*,row_number() over(partition by PARAID order by rowid) rn from sms_sys_param_test)
where rn>1)

删除重复的记录.

参考文章:http://www.adp-gmbh.ch/ora/sql/analytical/row_number.html

[@more@]

ITPub博客

oracle9i以后排重的好办法

最新文章