oracle delete query taking too much time

Question

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.

Answer 1

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).

Answer 2

it could be that your table is related to multiple tables have a huge row count.