Re: How does unique index work?

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?

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

> 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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB