Re: How does unique index work?

Sorry for the confusing question. My question was based on the premise that a UNIQUE index could underlay a deferrable UNIQUE constraint. Obviously I should not be responding to news groups past my bed time.

That index range scan must be potentially expensive on a large index. Thanks for explanation.
Mike

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3fceba3d$0$20482$afc38c87_at_news.optusnet.com.au...
>
> "Michael J. Moore" <NOhicamelSPAM_at_comcast.net> wrote in message
> news:72xzb.297654$275.1024876_at_attbi_s53...
> >
> > This will depend on of the constraint is currently deferred at the time
of
> > the insert.
> > If the constraint is not deferred, I believe it works like this ...
> > There is no need for a full scan, Oracle simply walks the branches of
the
> > index. In most cases this will take 3 i/o's or fewer to get to the index
> > leaf that contains the potentially duplicate data value. If it finds a
> > duplicate, that's the end of the story, throw and error and roll back
> (undo)
> > the transaction. Nothing needs to get moved to the rollback segment
> because
> > nothing gets inserted. As for when the constraint is deferred, well ...
I
> > don't really know ... maybe into temp? ... it will be interesting to see
> > what the answer is.
> >
>
>
> I don't quite get the grammar: "When is the constraint deferred? ... Maybe
> into temp?"
> One is temporal, and one is physical.
>
> Are you therefore asking *how* Oracle defers constraint checking? Where,
for
> example, does the row you insert go when the constraint is deferred?
>
> Because if so, the answer is gob-smackingly simple: the row goes into the
> table. It has to. From memory, there's also an insert into the
(non-unique)
> index used to enforce this deferrable constraint. In short, there's not a
> jot of difference between an insert into a table with a deferrable
> constraint compared to one with a non-deferrable one.
>
> The difference is only at commit time: An index spot check is done to
ensure
> uniqueness if it's non-deferrable, and an index range scan is done if it's
> deferrable.
>
> That's all. There's otherwise nothing different physically about them at
> all.
>
> Regards
> HJR
>
>
Received on Thu Dec 04 2003 - 23:23:00 CST