SQL optimization: deletes taking a long time
- by Will
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
