Re: How does unique index work?

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.

Mike

"John" <jbradshaw777_at_yahoo.com> wrote in message news:f2f59d82.0312031807.4cf31d42_at_posting.google.com...
> Strange question. But can somebody shed some light on the internal
> workings of an unique index?
>
> 1. Insert 1 row into a table with an unique index on it.
> 2. Old data and index blocks that the insert statement touched are
> move to rollback segment.
> 3. ORALCE enforces the uniqueness due to the index. How? By doing a
> full scaning on the table column and the index?
> 4. Insert another row into the table. How does ORACLE enforce the
> uniqueness? ORACLE does not need to look up the rollback segment to
> check for the uniqueness, right?
>
> Thanks!
>
> John
Received on Wed Dec 03 2003 - 20:33:39 CST