Im looking for expert advise here. We are in a situation where we are deleting data from a parent table that has no associated child records but RI is enabled between parent and child tables. The child table is huge one it has more than billion records. Though the parent records which are part of DELETE have no child records, the DELETE is taking very long ( more than 20 hrs but still running). we are looking for alternative approaches to accomplish this.
Option#1: DELETE parent while RI constraint in enabled ( taking more than 20 hrs but still running)
Option#2: We are looking at disable RI constraint and drop partition from parent table and eventually enable RI Constraint. But we are not sure how long ENABLE constraint takes on billion row tables and whether this is really better approach than Option#1.
Option#3: Restore the database before snapshot.
Can you pls advise which options are better? or any other better options ?
Appreciate your reply.
First question:
Are you really, really sure that this is slow because of the FK? There's no triggers on the parent table? No other DML which might be blocking the delete?
Second question:
Have you got an index on the child table on the FK columns?
If the answer's no, then Oracle Database will do a full scan of the child table
for each row you delete from the parent.
Here's a small example. There's an unindexed FK from T2 -> T1. There's no rows in T2. The delete from T1 removes two rows:
create table t1 (
x int primary key
);
create table t2 (
x int references t1 (x)
);
insert into t1 values (1);
insert into t1 values (2);
commit;
alter session set tracefile_identifier = chris;
exec dbms_monitor.session_trace_enable();
delete t1;
exit
Once you've formatted the trace file with tkprof or similar, you'll see something like:
select /*+ all_rows */ count(1)
from
"CHRIS"."T2" where "X" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 0 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=14 us)
0 0 0 TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 time=4 us cost=2 size=13 card=1)Notice that the "Execute" call has a count of 2. That's once for each row in the parent. Creating the index on t2 (x) avoids the full scan of the child.
If none of the above helps and you still want to try disabling the constraint, remember that your data is unprotected while the delete runs. So in theory you should do this in an outage...
You can speed up the enabling the constraint by using the novalidate option. This doesn't check existing rows meet the constraint, so will be significantly quicker than a regular enable. Though it does leave the chance you've got dodgy data in there. Particularly if you decided to risk disabling the FK while the app is still running!