Re: How does unique index work?

ctcgag_at_hotmail.com wrote in message news:<20031204115549.936$lt_at_newsreader.com>...
> jbradshaw777_at_yahoo.com (John) wrote:
> > 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.
>
> I don't think this is correct, although I see it all the time. Does
> Oracle copy data blocks into rollback wholesale, or does it simply write a
> delta into the rollback?

Well, it is a quick and dirty way of describing how ORACLE preserve the "before image" of the data. In reality for sure it is much more involved than that.

> > 3. ORALCE enforces the uniqueness due to the index. How? By doing a
> > full scaning on the table column and the index?
>
> No, a index look-up suffices, and of course it uses the index mechanism,
> not a full scan.
>

Makes sense. Yes.

> > 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?
>
> Did the first insert commit? If not, was it in the same transaction or
> a separate one?
>
> If the first isn't committed, and the two inserts are different
> transactions, then I think it does go to rollback. From some experiments I
> did a while ago (details forgotten), it seems like it does two lookups. It
> does a read-consistent look-up and dirty-read look-up. If both agree that
> the new row is unique, then it goes through. If they both agree it's not
> unique, it throws an error. If they disagree, then it blocks.
>
> Xho

I did mean the first has not yet committed and a different transaction comes in and does an insert. Can you explain this further? You mean the 2nd transaction actually does a dirty read on the stuff not yet committed by the first? You mean ORACLE actually compares 3 values, old data and new data from the 1st and the data being inserted by the 2nd? How do you determine ORACLE is doing a dirty read? Why is dirty read necessary?

This is the kind of stuff that, like Ramsfeld said, 'you don't know that you don't know'. I have always been afraid of that. Please do comment further.

John Received on Fri Dec 05 2003 - 01:03:28 CST