Re: How do a non unique index enforces primary key constraint?

hypermodern wrote:

> Hi all
> I am preparing for OCP. I have came across this in the student book
> that a non-unique index can enforce a primary key constraint. I don't
> get it. Can anybody explain this please.
>
> Thanks
>
> Fast Eddy

First, the index of whatever type has nothing to do with "enforcing" the constraint. That is just sloppy terminology, which I myself am guilty of using in the past.

It is more accurate to say that the constraint enforces primary keyness or uniqueness (because what you mention also happens with unique constraints).

The question is: *how* does it do that. If you insert a new record into the table, how does the constraint know whether it violates uniqueness or primary keyness? By looking at the records which exist in the table already.

But that would mean every new insert triggers a scan of the entire table... and that obviously would be hopeless from the performance perspective. So we build an index on the primary key. Now every new insert only has to scan the index to quickly find out whether there is a pre-existing value which precludes the addition of the new one or not.

Now if that index were non-unique, we would indeed have to scan through the index, and that sounds like we're doing a lot of work again. But if the index is truly unique, we can jump straight into the part of the index where the new entry ought to go, and if we spot anything in that precise spot, we immediately know that the constraint is violated.

That, at least, WOULD be the case if Oracle hadn't been so smart about it. That an index is declared to be non-unique when it is created is one thing. If you were then only to insert unique values into the TABLE, the *contents* of the index would actually be unique too, and never mind what the definition of the index happens to say. If I know the contents of the index are unique, I could treat it as though it were unique for the purposes of checking whether a new table entry violated that uniqueness.

And that is precisely what the optimiser in 8.0 and above does. Because the primary key or unique constraint is actually enforced, the optimiser knows that the contents of the index on those columns is content-unique, and that it can treat it as definitionally-unique accordingly.

So, the happy ending to this story is that as far as the optimiser is concerned, it makes no difference whether the index is unique or non-unique. The validating of new entries needed to conform to the requirements of the constraint can be done as efficiently in either case.

Managerially, however, it makes sense to get Oracle to generally use non-unique indexes, which you can do easily enough by making the constraint 'deferrable initially immediate'. That is because you can't switch off a unique or primary key constraint if there's a thumping great index there that has been declared to be unique. The constraint might not check new arrivals to the table, but the new arrivals will still fail to get into the table because the index refuses to permit duplicate data. To get around this logical nonsense, Oracle has a simple rule: if you disable a constraint that relies on a unique indexes for its checking, the index must be dropped. Which it does with ruthless efficiency: there are no warnings, no confirmations sought. The entire index just ceases to be.

Which means, of course, that re-enabling the constraint requires the entire index be rebuilt. And that could take a long time (and the table is locked for the duration of that rebuild, and hence no user DML on it is permitted. It is, in short, a very expensive proposition).

On the other hand, if you disable a constraint that uses a non-unique index for its checking, there is no logical incompatibility between the constraint not enforcing uniqueness and the index permitting it. Therefore, there is no need for the index to be dropped -therefore there is no need for it to be rebuilt, or for the table to be locked for long periods.

A non-unique index on a unique or primary key constraint is therefore of equal performance to its unique index cousin, but managerially a lot more convenient. My advice is therefore to always make your primary and unique keys deferrable. Then you get the non-unique indexes.

Regards
HJR Received on Wed Oct 20 2004 - 16:33:10 CDT