database - 使用我的 ORACLE 数据库请求提交每 200 个客户端的最佳方法是什么

Question

我的数据库中有一个每天启动的请求。它用于最多删除大约 750 个客户端的行。现在我们想保留该请求，但我们需要每 200 个客户端进行一次提交。

这是原始请求:

delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR);
delete from MYSCHEMA.TABLE2 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR);

如您所见，要删除的客户 ID 列表在另一个表中。我们通过选择请求获取它。

select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR

该列表最多可以包含 750 个客户端 ID。因此，为了 promise 每 200 个客户，我已经这样做了:

declare i number := 0;
begin
  for r in (select ID_CLI from MKTMLF.TMP_ID_CLI_SUPPR)
  loop
    delete from MYSCHEMA.TABLE1 where id_cli = r.id_cli;
    delete from MYSCHEMA.TABLE2 where id_cli = r.id_cli;
    i := i+1;
    if mod(i, 200) = 0 THEN   
          COMMIT;
    end if;
   end loop;
 commit;
end;

但我的同事告诉我这是个坏主意，因为如果我们要删除 750 个 ID，这两个请求将是 750 次，所以 750*2 = 1500 个请求!他告诉我使用 ROWNUM 来获取前 200 个 ID，然后提交其他 200 个等等......所以我尝试了一下，它看起来像这样:

declare listTotal number := 0;
begin
    select count(1) into listTotal from MKTMLF.TMP_ID_CLI_SUPPR;
    delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR where ROWNUM < 201);
    delete from MYSCHEMA.TABLE2 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR where ROWNUM < 201);
commit;
if listTotal > 200 THEN
    delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from (select tmp.*, rownum r from MKTMLF.TMP_ID_CLI_SUPPR tmp) where r > 200 and r < 401);
    delete from MYSCHEMA.TABLE2 where id_cli in (select ID_CLI from (select tmp.*, rownum r from MKTMLF.TMP_ID_CLI_SUPPR tmp) where r > 200 and r < 401);
end if;
commit;
if listTotal > 400 THEN
    delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from (select tmp.*, rownum r from MKTMLF.TMP_ID_CLI_SUPPR tmp) where r > 400 and r < 601);
    delete from MYSCHEMA.TABLE2 where id_cli in (select ID_CLI from (select tmp.*, rownum r from MKTMLF.TMP_ID_CLI_SUPPR tmp) where r > 400 and r < 601);
end if;
commit;
if listTotal > 600 THEN
    delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from (select tmp.*, rownum r from MKTMLF.TMP_ID_CLI_SUPPR tmp) where r > 600 and r < 751);
    delete from MYSCHEMA.TABLE2 where id_cli in (select ID_CLI from (select tmp.*, rownum r from MKTMLF.TMP_ID_CLI_SUPPR tmp) where r > 600 and r < 751);
end if;
commit;
end;

所以我问自己实现这一目标的最佳方法是什么？我发现第二种方式有点太复杂但也许更快？也许您有其他更好的方法？

Answer 1

你说你想每 200 次提交一次......“因为有时它会在结束前失败”

根据该信息，我建议使用 ERROR_LOGGING 子句并将其保存在单个语句中。我不认为将其分解为每 200 个提交一次是处理这种情况的最佳方法。

以下是我的建议:

1) 为您的两个表创建一个错误表:

  EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('TABLE1', 'TABLE1_ERRLOG');
  EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('TABLE2', 'TABLE2_ERRLOG');

(或您正在处理的每个表 1 个)

这是一次性设置..并且不必每天重做。

2) 让你的“日常工作”运行以下删除语句......包括 LOG ERRORS 子句:

  delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR) 
     LOG ERRORS INTO TABLE1_ERRLOG ('Daily Delete1') REJECT LIMIT 750 ;  

您可以为 REJECT LIMIT 输入任何您想要的数字..我现在输入 750..因为您提到您每天处理多达 750 个？这将允许脚本尝试删除所有内容......并报告所有失败的内容。如果您选择一个较低的数字，它会在达到那么多失败的删除后停止。调整以满足您的要求。 ;)

  delete from MYSCHEMA.TABLE2 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR)
     LOG ERRORS INTO TABLE2_ERRLOG ('Daily Delete2') REJECT LIMIT 750 ;  

3) 让作业在运行后查看这 2 个错误日志表，如果记录存在则做出相应响应 ...

您可以查看它们并按照您的意愿处理它们...然后在您想要重试时重新运行 DELETE。

(注意:删除一个不存在的记录就可以了，在这种情况下它不会记录任何错误...)

请注意，此 ERROR_LOGGING 子句存在于 INSERT、UPDATE 和 DELETE 语句中。而且你只需要每个基表有 1 个错误表......不管你运行的 INS/UPD/DEL ..

换句话说......在创建 TABLE1_ERRLOG 之后......然后你可以运行:

     delete from MYSCHEMA.TABLE1 where id_cli in (select ID_CLI from MYSCHEMA.TMP_ID_CLI_SUPPR) 
     LOG ERRORS INTO TABLE1_ERRLOG ('Daily Delete1') REJECT LIMIT 750 ;  

和

     INSERT into MYSCHEMA.TABLE1 ( select * from ... whatever ..)
        LOG ERRORS INTO TABLE1_ERRLOG ('Daily Insert1') REJECT LIMIT 750 ;  

还有:

     UPDATE MYSCHEMA.TABLE1 set some_col = some_value
        where <some condition>
        LOG ERRORS INTO TABLE1_ERRLOG ('Daily Update1') REJECT LIMIT 750 ;  

而且它们都会将错误转储到同一个错误日志表中:TABLE1_ERRLOG您可以查看列:ORA_ERR_TAG$ 以查看它是 Del、Ins 还是 Upd ..(即您发送的“评论”。我在上面的示例中使用了“Daily Delete1”、“Daily Insert1”和“Daily Update1”)

甲骨文 10: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8005.htm

LOG ERRORs 子句示例:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ

甲骨文 11: https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_8005.htm#SQLRF01505

LOG ERRORs 子句示例:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#i2121671