Re: How does unique index work?

"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 Wed Dec 03 2003 - 22:38:21 CST