Re: How does unique index work?

>
> 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
It helps, as always, Howard.
When ORALCE is scanning the index or table to enforce the uniqueness, does the read-consistency issue come into play? Scanning implies a 'select' action, right?

Let's say there are 2 insert sessions going against the same table concurrently. One session should check for uniqueness by scanning the rollback generated by the other (for those modified blocks), right? But each session will not scan its own rollback data.

What if one session takes much longer than the other? The longer session will need to access the entire rollback data generated by the shorter session, which has committed. What happens when the rollback generated by the shorter session is overwritten by other transactions? Where would the longer session go to to check for the uniqueness of the index, now that the rollback data generated by the shorter sessionis is gone?

I hope there is hole in my thinking, otherwise it would imply you can get the "snapshot too old" error with a simple insert - as oposed to the insert into .. select ... type of statement.

John Received on Thu Dec 04 2003 - 19:39:51 CST