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

try to use SELECT instead of delete to see is DELETE operation real bottleneck

Answer 2

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 3

Try avoid Subselect in you quires and use INNER JOIN instead

Answer 4

While it may not be any faster it would be easier to read if you did:

DELETE FROM item i 
WHERE n NOT IN (SELECT n FROM item_queue)
AND n NOT IN (SELECT n FROM tool_queue)

Answer 5

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.

Answer 6

Make sure you don't have a constraint on a large table that references your item table. That can be a real slowdown in the case of deletes.

Answer 7

You can't really get a good answer for this without doing additional work.

After the SQL statement iteself, the most importasnt thing is that the statistics for the objects (tables and indexes in this case) are representative.

Then you really need to look at the access path that oracle chooses - many ways to do this.

Try

EXPLAIN PLAN SET STATEMENT_IS = 'SQL01' FOR
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);

Then

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Maybe post the result here.

As you try different things - such as re-writing the query, modifying indexes etc, you will notice that the access path changes.

This is a rather complex area - and you will need to study / practice.

Deletes are slow for a number of reasons, but a big factor is the maintenance of the indexes on the table. However, in your case, you say there are only 10k rows which is pretty small. (BTW you did not give timings here. Is it taking 1, 10 or 100 seconds at the moment? and what do you want to achieve? ) So I would be focusing on the access path through the larger tables.

My first approach would might be:

DELETE FROM item i 
 WHERE NOT EXISTS 
(SELECT NULL
 FROM item_queue q,
      tool_queue g
  where q.key = g.key      -- if the tables are related
    AND q.n=i.n) ;

But like I say there are many factors here.