oracle亿级表删除其中70W的数据

[yeats1989523]

领导让我把几千万的数据都合成了一张表，导致现在该表数据有亿级，在建立主键的时候发现有重复的70W数据，要求删除。我看到之前有人说开10个job去处理。但是不知道如何修改这句sql语句来分10个job处理。
我的delete语句如下:
delete from event_xx e where e.event_guid in (select event_guid from event_xx group by event_guid having count(event_guid)>1) and rowid not in (select min(event_guid) from event_xx group by event_guid having count(event_guid)>1);

之前别人提供参考的分10个job的命令如下：
DECLARE
x NUMBER;
BEGIN
FOR i IN 0 .. 9
LOOP
DBMS_JOB.submit (
x,
'begin DELETE tabacca t1
WHERE EXISTS
(SELECT 1
FROM (SELECT id
FROM (SELECT SUBSTR (TO_CHAR (ROWNUM), -1) rn,
id
FROM temptablea
WHERE TYPE = ''1'')
WHERE rn = '''
|| i
|| ''') t2
WHERE t2.id = t1.id);

COMMIT; end;'
);
END LOOP;
END;

[yeats1989523]

引用 1 楼 baidu_36457652 的回复:

可以把这个放到 一个表或者临时表
SELECT event_guid,ID FROM (
select ROWID AS ID,event_guid,ROW_NUMBER()OVER(PARTITION BY event_guid ORDER BY 1) AS RN from event_xx )KK
WHERE KK.RN=1
然后做删除处理

我放到临时表以后，想根据rowid来进行删除，写了一段但是报错，能帮忙看下吗谢谢：

declare
cursor my_cur is 
select id1 from test2018;
type rec is table of my_cur%rowtype;
recs rec;
begin
open my_cur;
while(ture) loop
fetch my_cur bulk collect into recs limit 100;
forall i in 1 .. recs.count
delete from event_xx where rowid= recs(i);
commit;
end loop;
close my_cur;
end;

[yeats1989523]

提示delete这一行表达类型错误

[sxq129601]

类型改成TYPE rec IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; 循环改成FOR I IN recs.FIRST .. recs.LAST LOOP 试试吧

[yeats1989523]

引用 4 楼 sxq129601 的回复:

类型改成TYPE rec IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; 循环改成FOR I IN recs.FIRST .. recs.LAST LOOP 试试吧

不行，这一行FOR I IN recs.FIRST .. recs.LAST LOOP报了数字或值错误

[学海无涯-回头是岸]

delete from event_xx where rowid= recs(i); 改成 delete from event_xx where rowid= recs(i).id1; 而且你的临时表和 这个语句块 至少要在一个 会话里面。

[学海无涯-回头是岸]

可以把这个放到 一个表或者临时表
SELECT event_guid,ID FROM (
select ROWID AS ID,event_guid,ROW_NUMBER()OVER(PARTITION BY event_guid ORDER BY 1) AS RN from event_xx )KK
WHERE KK.RN=1
然后做删除处理

[zcs_zzh]

可利用分析函数得到重复的数据的ROWID，把ROWID保存到表中，用ROWID做条件删除数据就快多了。 本问题的关键在于查询重复的数据，要删除的数据确定了，删除就快了。

[大眼鱼叔叔]

再新建一张临时表，用merge再导一遍，应该就没问题了

[覌海雲逺]

delete from event_xx a where a.rowid>(select min(b.rowid) from event_xx b where b.event_guid =a.event_guid );
试试这个去重

[覌海雲逺]

delete from event_xx a 
where a.rowid>(select min(b.rowid) from event_xx b where b.event_guid =a.event_guid ) 
and a.event_guid in (select event_guid from event_xx group by event_guid having count(event_guid)>1);
刚才忘加后面的条件了