Re: Deferred contraints - DDL

"jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.2498cfffa925c6ea98a505_at_news.individual.net...
> 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. .

-- 
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.html