Re: How does unique index work?

> This will depend on of the constraint is currently deferred at the time of
> the insert.

Thanks. We are talking about a 'unique index' as opposed to a unique constraint here, of course. As long as the index is normal, I don't think the uniqueness checking can be deferred.

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

OK. Let's say, ORACLE scans the index and decides the data is good and inserts the data. So now, the old data and index blocks must be copied to rollback, right?

What happens next when you insert another row and ORACLE again does the uniqueness checking? Would it ever look up the rollback? I thinking is that it would not. If the 2nd row is the same as the 1st, it will be rejected - even though the 1st row has not yet been committed.

But I am really not sure if this is how ORACLE constraint checking works.

John

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 Thu Dec 04 2003 - 12:05:20 CST