Delete the same set of values ​​from multiple tables

Question

I would not want to repeat the same subquery again and again 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; / 

The alternative I 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 do you think of this alternative? is there a more efficient way to do this?

Answer 1

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

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

Cut the table when done.

Answer 2

If you can do this in pure SQL than in SQL, PL / SQL is not needed. With every SQL call in PL / SQL (or vice versa, but in this case less) there is overhead associated with each context switch between the two engines .. p>

Now, having said that, if you must do this in PL / SQL, then you can reduce the context switches by combining the entire collection into a DML statement in one operation.

Typically, cursor for loop uses an implicit bulk collect limit 100 , which is much better than an explicit cursor.

But, this is not just mass collection , we are dealing with operations that we will subsequently do in the array, which we added gradually. We could further improve performance using the FORALL statement and BULK COLLECT .

IMO, it is best to do this in pure SQL. If you really want to do this in PL / SQL, do it as I mentioned above.

I would go with the SQL approach, and since you have repeated the same subquery, I would use QUERY RESULT CACHE . Oracle 11g Introduces QUERY RESULT CACHE .

In your subquery:

 SELECT /*+ RESULT_CACHE */ <column_list> .. <your subquery>... 

For instance,

 SQL> EXPLAIN PLAN FOR 2 SELECT /*+ RESULT_CACHE */ 3 deptno, 4 AVG(sal) 5 FROM emp 6 GROUP BY deptno; Explained. 

Look at the output of the plan tables:

 SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- Plan hash value: 4067220884 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 | | 1 | RESULT CACHE | b9aa181887ufz5341w1zqpf1d1 | | | | | | 2 | HASH GROUP BY | | 3 | 21 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=2; dependencies=(SCOTT.EMP); name="SELECT /*+ RESULT_CACHE */ deptno, AVG(sal) FROM emp GROUP BY deptno" 15 rows selected. 

Answer 3

An alternative could be:

 begin for s in (select distinct id from subquery) loop delete from t1 where t1.id = s.id; delete from t2 where t2.id = s.id; delete from t3 where t3.id = s.id; end loop; end; / 

This is generally a controversial method, as line by line processing is slower than performing multi-line operations. But if the point is that the subquery very slow, this could be an improvement.

Answer 4

There seems to be a dependency between the tables t1 , t2 and t3 in the id field.

You can probably solve this with the delete trigger on t1 to delete entries in t2 and t3 .

I don’t know the exact tables affected by your subquery, perhaps you can set the update flag (for example, the date field) in the next table t0 and delete the entries on t1 , t2 and t3 using the update trigger in table t0 if the date field changes.

The advantage of this solution is database consistency and pure sql.

Answer 5

I think you cannot use delete for multiple tables at a time. But one way could be to use factoring subqueries (with a sentence). I don’t have an exact answer at the moment, but I think the suggestion might help. I will be back with the exact request as soon as I can devote some time. In addition, if you have a requirement to simultaneously remove the auxiliary request identifier from the subtask tables, then this will be easier in the sense that you can create a foreign key based on the delete cascade.

Hope this helps.