How do a non unique index enforces primary key constraint?
hypermodern
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
Howard J. Rogers
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
Mark D Powell
See the Concepts manual chapter on Contraints. A non-unique index
enables deferring enforment of the PK constraint until commit time. A
unique index would prevent the insertion of a duplicate row at insert
time.
HTH -- Mark D Powell --
hypermodern
Fast Eddy
Mark....@eds.com (Mark D Powell) wrote in message news:<2687bb95.04102...@posting.google.com>...