CODEWITHSUNDEEP
sqldatabaseoracle-database
UndefinedReference
UndefinedReference

Reputation: 1253

Temporarily Disabling Constraints in Oracle Database

I have an Oracle SQL delete statement that is taking a very long time to run. It's very simple, something like:

delete from table
where fy = 2016

I also manually tried deleting one of the ~2500 rows I need to get rid of in TOAD and it's taking about 6 minutes to delete a single row.

I think the reason for this is that the table has a lot of constraints on it, so I'm wondering if I can somehow change my delete statement in such a way that it can bypass the constraints or disable them temporarily in order to do it much faster? If so, how can I re-enable them afterwards?

Upvotes: 1

Views: 4867

Answers (2)

fatihn
fatihn

Reputation: 147

As you stated; the reason is foreign keys to that table. You should disable that keys before delete and enable them after delete. You can use following script to enable/disable keys of a specific table.

declare
  l_constraint_sql varchar2(1024);
  l_op_type varchar2(10) := 'ENABLE';

begin
  -- Test statements here
  for r in (select a.*
              from user_constraints a, user_constraints b
             where a.constraint_type = 'R'
               and a.r_constraint_name = b.constraint_name
               and a.r_owner = b.owner
               and a.owner = 'FAYDIN' --schema
               and b.table_name = 'GEN_KOD' --table you want to disable constraints
            ) loop
    l_constraint_sql := 'alter table ' || r.TABLE_NAME ||
                        ' ' || l_op_type || '  constraint ' || r.CONSTRAINT_NAME || '';

    --uncomment following line to execute                    
    --execute immediate l_constraint_sql;
    dbms_output.put_line(l_constraint_sql);

  end loop;
end;

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

6 minutes to delete a single row does not sound like an issue with constraints on the table. There are three reasonably likely mechanisms that would cause that level of performance problem. Most to least likely in my experience

  1. The table is the parent to one or more large child tables. The child tables have an enforced foreign key reference to the parent table but the foreign key column is not indexed in the child table. If this is the case, the issue is that Oracle has to do a full scan of the table every time you delete a row from the parent in order to verify that there are no orphans. You could go to each child table and drop the foreign key but it would almost certainly make more sense to index the foreign key column in the child table(s). It is very rare that you want to have unindexed foreign keys.
  2. There is a trigger on the table and the trigger is doing something that takes 6 minutes. You'd have to look at the trigger code to see exactly what was taking so long there.
  3. You are doing a single-row delete as a transaction and you have an on commit materialized view that needs to be updated as a result of the change. It's hard to come up with a way to build something like this that would take 6 minutes to run but there are certainly ways to make this slow.

Upvotes: 4

Related Questions