SQL optimization: deletes taking a long time

Question

I have an Oracle SQL query as part of a stored proc:

DELETE FROM item i 
 WHERE NOT EXISTS (SELECT 1 FROM item_queue q WHERE q.n=i.n) 
 AND NOT EXISTS (SELECT 1 FROM tool_queue t WHERE t.n=i.n);

A bit about the tables:

• item contains about 10k rows with an index on the n column
• item_queue contains about 1mil rows also with index on n column
• tool_queue contains about 5mil rows indexed as well

I am wondering if the query/subqueries can be optimized somehow to make them run faster, I thought that deletes were generally fairly fast

Answer 1

Turn your delete into a select, then you can check and optimize the query part.

Otherwise note - deletes are not the fastest thing around. LOTS of things happen on a delete.

OTOH I seriously think.... the problem is the two subqueries. What does the query plan look like?

Answer 2

Try something like:

 DELETE FROM item WHERE n NOT IN 
     (SELECT i.n FROM item i INNER JOIN item_queue q ON i.n = q.n
      UNION SELECT i.n FROM item i INNER JOIN tool_queue t ON i.n = t.n)

Your correlated sub-queries are running 10K times each in your example. This technique will run two INNER JOIN queries to get the list of "n"s to delete.

You may need to fiddle the SQL a bit; I'm not familiar with the Oracle dialect.