Re: Deferred contraints - DDL
Date: Wed, 10 Jun 2009 00:55:46 -0700 (PDT)
Message-ID: <996775b9-6bb6-43b8-b1db-424cae3b2e4d_at_j12g2000vbl.googlegroups.com>
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.
Jeremy,
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:
http://oracle-randolf.blogspot.com/2008/07/deferrable-constraints-and-direct-path.html
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Wed Jun 10 2009 - 02:55:46 CDT