Re: Implementing Constraints

"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message news:3B40CA1A.FCB333FC_at_attws.com...
> "Howard J. Rogers" wrote:
>

[snip]

> And having worked with these and grown to dislike them it also means you
 need
> huge rollback segments because since you can not doing incremental commits
 you
> must hold the entire load.
>
> Daniel A. Morgan
>

There's nothing to stop you doing incremental commits. A deferred constraint simply means the thing is checked when you commit and not when you insert (or update). Nothing there to say that commits are somehow suspended until the entire load is completed.

Now, if you're thinking about self-referencing foreign keys, it's true that an incremental commit would stuff things up, and negate the whole point of a deferred foreign key constraint. But that's the only case I can think of where what you say is true.

Personally, I always advocate that every primary key and unique key constraint should be declared deferrable (whether actually deferred or not is irrelevant), so that my indexes don't perform the Great Disappearing act on me without my say-so the second I disable the constraint.

And deferring such constraints would have precisely zero impact on rollback segment size.

HJR Received on Sat Jul 21 2001 - 16:29:49 CDT