Oracle IN Condition very slow

Question

Hi,

I am trying to execute the following statement on a table containing 10,000 rows but the query is executing forever.

delete from Table_A where col1 in ('A','B','C') and col2 in ('K','L','M') and col3 in ('H','R',D')

Please can anyone assist!

Thanks A

Answer 1

It looks as if another session has locked one of the rows you'd like to delete.

Is somebody else working on the same table (with transactions that last more than a few seconds)? Or do you have another tool or session open where you haven't committed your changes?

Update:

Another problem are foreign keys that aren't properly index: If other tables have a foreign key to the table where you want to delete the rows, and if the foreign key column in those tables isn't indexed, then Oracle will try to lock those tables. This could be the cause. If this is the case, index those columns.

Answer 2

Another possible reason for a database to hang is if the archive log destination is full. Query the V$SESSION_WAIT and V$SESSION_EVENT views to see what your session is waiting for.