Oracle Delete Query Taking Too Much Time

oracle delete query taking too much time

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

Delete statement was very slow in Oracle

There can be many reasons:

• Server load (unlikely because the SELECT is fast)
• Triggers (see here how to list them for a table).
• Foreign keys (List of foreign keys and the tables they reference)
• A lot of data in each row (LOBs, many columns).
• Someone is locking rows in the table that you'd like to delete (or the whole table). See this blog post how to list locks. This discussion might also help.

If the foreign keys are the problem, the usual solution is to add indexes on the foreign column: For each delete, Oracle needs to check whether this would violate a foreign key relation.

Delete query takes too long time to execute

Whilst indexing the column you're filtering against may help (it may not; it depends on the percentage of rows in the table that are affected, the size of the table, how the data is organised within the table etc.), if the delete was previously running in an acceptable time before but it's not any more (using the exact same filter condition), then something must have changed.

My guess is that you have a lot more data in the table than before, but there could be a variety of factors, such as memory, a deleted index, etc etc. It would help if you had the execution plan from when the delete was running ok along with the current execution plan. That might throw up some issues.

Delete is taking considerable amount of time

For sure, if you loop 10k times, all those DBMS_OUTPUT.PUT_LINE calls will slow things down (even if you aren't doing anything "smart") (and I wonder whether buffer is large enough). If you want to log what's going on, create a log table and an autonomous transaction procedure which will insert that info (and commit).

Apart from that, are tables properly indexed? E.g. that would be cola column in tab2 table (in code you posted). Did you collect statistics on tables and indexes? Probably wouldn't harm if you do that for the whole schema.

Did you check explain plan?

Do you know what takes the most time? Is it a ref cursor query (so it has to be optimized), or is it deleting itself?

Can't you avoid loop entirely? Row-by-row processing is slow. For example, instead of using ref cursor, create a table out of it, index it, and use it as

create table c_invoice as
  select * from a join b on a.col = b.col;

create index i1inv_col1 on c_invoice (col1);

delete from tab2 t
where exists (select null 
              from c_invoice c
              where c.col1 = t.cola
             );

Why is this DELETE query so slow on Oracle?

Baring in mind the considerations you gave, the only thing I can think of is some kind of UNINDEXED REFERENCE CONSTRAINT referring to this table. Try running this script against the table, it should give you a report with any unindexed references it finds. There's certainly no reason why this operation should take close to this long.

Oracle delete query taking long time to delete the records from the table

The fastest way to delete the table might be DROP TABLE test_table command, and recreate the table using CREATE TABLE... command. DROP TABLE... will drop the table immediately. Well, actually it will move the table into recyclebin. You should PURGE RECYCLEBIN if want to completely remove the table.

Other way to delete the data in the table is to use TRUNCATE TABLE.... This is a little slower than DROP TABLE..., however, much faster than DELETE FROM.... Since there's no way to rollback the data after you truncate the table, you should be careful when you use TRUNCATE TABLE.

Delete suddenly taking a long time

please review the answers of this SO Question: "oracle delete query taking too much 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).

I would check #2 and #3 first, they are the easiest to diagnose.

---

Related Topics