Re: Deferred contraints - DDL

From: Jonathan Lewis <>
Date: Tue, 9 Jun 2009 21:13:50 +0100
Message-ID: <>

"jeremy" <> wrote in message
> 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.
> As I understand it, in order to be able to alter the sesion and make
> constrainst deferred, they must be created as "deferrable" and that you
> cannot simply issue an "alter" command to achieve this.
> I was just wondering if (of course to greatly minimise my work :) )
> anyone had written a script to run through a schema and generate the DDL
> to drop and recreated constraints with identical properties but with the
> deferred option? Actually thinking about that now (as the requirement
> has kind of crystallised now), it is I would imagine no more than 30
> minutes work so no need to reply unless you can really spare the time.
> --
> jeremy

Don't make unique/primary key constraints deferrable unless you have a good reason to. They have to be supported by non-unique indexes, and this affects the costing (so plans may change), latch activity, and the volume of undo and redo. .


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Tue Jun 09 2009 - 15:13:50 CDT

Original text of this message