Here is my observation regarding deferred foreign keys which I am not able to understand:
- We have 3 tables : TP1, TC1 & TC2. TC1 and TC2 have foreign keys pointing to TP1 and foreign keys are defined as DEFERRED.
- We load huge amounts of data to these tables once a week and the insert takes unusually long time.
This is how we insert: (Case 1)
step 1: alter session set constraints=DEFERRED
step 2: run insert statements
step 3: alter session set constraints=IMMEDIATE
step 4: commit
- Step 2 above takes 10hours
- My impression is that Oracle will not do referential integrity check at step 2 because we are deferring constraints at the session level. But that does not seem to be the case. I used a different approach to see if Oracle is doing something different and the different approach I tried is:
(Case 2)
step 1: disable Foreign key constraints on TC1 and TC2 tables
step 2: run insert statements
step 3: enable Foreign key constraints on TC1 and TC2 tables
- In this approach step 2 took 1 hour when compared to 10 hours in earlier case.

As per theory I expect step2 in both cases to take same amount of time. Appreciate if someone can explain step 2 in Case 1 takes more time when compared to step 2 in Case 2.

PS:Excuse me if title doesn't suit the problem