oracle – 从多个表中删除相同的值集

例：

begin
    -- where subquery is quite complex and returns thousands of records of a single ID column
    delete from t1 where exists ( select 1 from subquery where t1.ID = subquery.ID );
    delete from t2 where exists ( select 1 from subquery where t2.ID = subquery.ID );
    delete from t3 where exists ( select 1 from subquery where t3.ID = subquery.ID );
end;
/

我发现的另一种选择是：

declare
  type id_table_type is table of table.column%type index by PLS_INTEGER
  ids id_table_type;
begin
  select ID
  bulk collect into ids
  from subquery;

  forall indx in 1 .. ids.COUNT
    delete from t1 where ID = ids(indx);

  forall indx in 1 .. ids.COUNT
    delete from t2 where ID = ids(indx);

  forall indx in 1 .. ids.COUNT
    delete from t3 where ID = ids(indx);
end;
/

您对此替代方案有何看法？有更有效的方法吗？

解决方法

对于每次运行,将子查询的结果插入临时表中.子查询只运行一次,每次删除都很简单：从mytable t中删除t.id in(从tmptable中选择id);.

完成后截断表.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!