Ajay Ajay - 2 months ago 9x
SQL Question

oracle delete query taking too much time

I have a query like

DELETE from tablename where colname = value;

which takes awfully long time to execute.
What could be the reason? I have an index on colname.


There could be several explanations as to why your query takes a long time:

  1. You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: issue SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT,
  2. There could be a ON DELETE TRIGGER that does additional work,
  3. Check for UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom that will help you determine if such unindexed foreign keys exist).