Re: How does unique index work?

"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

First, understand that uniqueness can be checked without a unique index (Oracle does it all the time for deferrable constraints).

Second, why do unique constraints require an index? Answer: they don't. It would be feasible to program something to enforce uniqueness without an index. But, given that you have no say about where rows in a table end up being physically stored, that would require programming a full table scan for every insert... which would be, to say the least of it slow. So Oracle demands that an index be there on a unique constraint, because that way it can jump in and out of the index (which is in a known, sorted order) instead of scanning a whole set of randomly-stored records.

The only difference between a unique index to enforce uniqueness and a non-uniqueness to do the same thing is the extent to which Oracle has to scan before it realises that uniqueness has been breached.

If you're inserting 'Bob, 750' (a unique combination), then we can dip into a unique index for those two fields, and the second we see an entry for 'Bob, 750' we know the constraint's been violated. With a non-unique index, there's no saying that the index isn't built on ename, salary and department. So there might be a Bob, 750, 10 and a Bob, 750, 20 in the index. We could therefore hump into the index at Bob, 750.... but we'd have to scan a few rows before we can determine that uniqueness is not violated. As soon as we encounter an index entry for 'Cathy', we know there can't be any more Bobs and can stop.

In theory, therefore, uniqueness enforced with a non-unique index requires a range scan of the index, whereas uniqueness enforced with a unique index can do the 'dip and dive' stuff. Dip into the index, spot the identical entry and dive back out again.

In practice, there's no difference in Oracle, because the opimiser realises that a non-unique index used to enforce a unique constraint is itself, in terms of its actual contents, unique. Therefore, the explain plan shows a range scan against the index, but internally it is treated as a dip and dive exercise.

Does that help?
Regards
HJR Received on Wed Dec 03 2003 - 22:31:37 CST