Re: Deferred contraints - DDL
Date: Tue, 9 Jun 2009 21:13:50 +0100
"jeremy" <jeremy0505_at_gmail.com> 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.
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. .
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Tue Jun 09 2009 - 15:13:50 CDT