Search code examples
oracle-databaseinner-joinsql-delete

Delete query with inner join executes infinitely


I am deleting rows from A table with the next query:

delete from 
  (select A.* from A 
  inner join B 
    on A.id = B.id 
  where B.note = '000000000000')

and this query executes infinitely (more than 20 minutes) while select query returns 9300 rows in 3.3 seconds:

select * from 
  (select A.* from A 
  inner join B 
    on A.id = B.id 
  where B.note = '000000000000')

Where is the problem?

P.S.: delete action has taken 40 minutes.

P.P.S.: According to "Which is Most Efficient when Deleting Rows: EXISTS, IN, or a VIEW" there is no difference in the described examples.

UPDATE: the situation reproduced again with the same symptoms "SELECT is fast, DELETE is slow"

DB-admins checked for blockings and found that there are two sessions are blocking each other, one session is sql-delete statement described above; the other one is not known but I suppose that it could be the same delete statement or another delete statement in related table.

This seems to be true according to Database Locking: What it is, Why it Matters and What to do About it:

Oracle’s implementation is to maintain separate versions of the related data blocks. The uncommitted changes made in one transaction are visible within the transaction that made them, while other sessions will see the old, unchanged version. In this way, isolation is maintained and select statements never block.


Solution

  • The answer is simple one session is blocking another that is why delete operation takes too much time:

    BLOCKER                           SID 'ISBLOCKING'  BLOCKEE                           SID
    ------------------------------ ------ ------------- ------------------------------ ------
    XXXXXXXXXX                        832  is blocking  XXXXXXXXXX                        124
    

    Now it is good to know how to avoid this situation but this is another question.