Re: Deferred contraints - DDL
Date: Wed, 10 Jun 2009 00:55:46 -0700 (PDT)
On Jun 9, 10:01 pm, jeremy <jeremy0..._at_gmail.com> wrote:
> Hi, following on from the thread earlier about deferred constraints. We
> are on 10gR2. I was not aware of the feature and have realised this
> could be very useful for us for some data-load type activities.
adding to Jonathan caveats: Using deferrable unique/primary key constraints supported by an non-unique index effectively disables the direct-load insert mode in 10gR2. In fact it's about the non-unique index used to enforce a unique constraint, not about the deferred mode. So even with an immediate unique constraint supported by a nonunique index the direct-path insert mode does not get used. This seems to be different in 11.1, by the way, where the direct-path insert mode gets used in this particular case (immediate constraint enforced by non-unique index), but still the deferred constraint will prevent the direct-path insert even in 11.1.
For more details, see my blog note:
Oracle related stuff blog: