Re: Deferred contraints - DDL

From: Randolf Geist <>
Date: Wed, 10 Jun 2009 00:55:46 -0700 (PDT)
Message-ID: <>

On Jun 9, 10:01 pm, jeremy <> 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:

SQLTools++ for Oracle (Open source Oracle GUI for Windows): Received on Wed Jun 10 2009 - 02:55:46 CDT

Original text of this message