Delete the same set of values from multiple tables

Question

I would like to not have to repeat the same subquery over and over for all tables.

Example:

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;
/

An alternative I've found is:

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;
/

What are your thoughts about this alternative? is there a more efficient way of doing this?

Answer 1

Create a temporary table, once, to hold the results of the subquery.

For each run, insert the results of the subquery into the temporary table. The subquery only runs once and each delete is simple: delete from mytable t where t.id in (select id from tmptable);.

Truncate the table when finished.