PL SQL大容量收集fetchall未完成

Question

我执行此过程来批量删除数据(3500万条记录(。您能理解为什么这个pl/sql过程在不退出的情况下运行，并且不会删除行吗？

create or replace procedure clear_logs
as 
CURSOR c_logstodel IS SELECT * FROM test where id=23;
TYPE typ_log is table of test%ROWTYPE;   
v_log_del typ_log;
BEGIN
OPEN c_logstodel;
LOOP   
FETCH c_logstodel BULK COLLECT INTO v_log_del LIMIT 5000;   
EXIT WHEN c_logstodel%NOTFOUND;
FORALL i IN v_log_del.FIRST..v_log_del.LAST   
DELETE FROM test WHERE id =v_log_del(i).id;   
COMMIT;
END LOOP;
CLOSE c_logstodel;
END clear_logs;

Answer 1

添加rowid而不是列名，exit when v_delete_data.count = 0;而不是EXIT WHEN c_logstodel%NOTFOUND;，并将区块限制更改为50000，使脚本能够在15分钟内清除3500万行

create or replace procedure clear_logs
as 
CURSOR c_logstodel IS SELECT  rowid FROM test where id=23;
TYPE typ_log is table of rowid index by binary_integer;
v_log_del typ_log;
BEGIN
OPEN c_logstodel;
LOOP   
FETCH c_logstodel BULK COLLECT INTO v_log_del LIMIT 50000;   
exit when v_log_del.count = 0;
FORALL i IN v_log_del.FIRST..v_log_del.LAST   
DELETE FROM test WHERE  rowid =v_log_del(i);  
exit when v_log_del.count = 0;
COMMIT;
END LOOP;
COMMIT;
CLOSE c_logstodel;
END clear_logs;

Answer 2

首先，当使用BULK COLLECT LIMIT X时，%NOTFOUND具有稍微出乎意料的含义。在这种情况下，%NOTFOUND实际上意味着Oracle无法检索X行。(我想从技术上讲，它总是让你获取下一个1，并且它说它无法填充1行缓冲区。(只需将EXIT WHEN%NOTFOUND移到FORALL之后。但实际上没有理由检索数据然后删除检索到的行。虽然一条语句的速度要快得多，但3500万行将需要显著的回滚空间。有一个安葬的解决方案
尽管Delate语句不常用，但它会像选择一样生成rownum。用户可以使用此值来限制处理的行数。因此，要进入给定的提交大小，只需限制删除时的行数：

create or replace procedure clear_logs
as 
k_max_rows_per_interation constant integer := 50000;
begin
loop   
delete 
from test 
where id=23
and rownum <= k_max_rows_per_interation;
exit when sql%rowcount < k_max_rows_per_interation;
commit;
end loop;
commit;
end; 

正如@Stilgar所指出的，删除成本高昂，意味着速度缓慢，因此他们的解决方案可能会更好。但这样做的优点是，在操作过程中，它基本上不会使表完全停止服务。注意：我倾向于使用更大的提交间隔大小，通常在400000-30万行左右。我建议你和你的DBA谈谈，看看他们认为这个限制应该是什么。记住，他们的工作是为典型的操作适当地调整回滚空间的大小。如果这在您的操作中是正常的，他们需要正确设置。如果您可以获得35M删除的回滚空间，那么这是您将获得的最快的回滚空间。