JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 18: Data Integrity.


When describing primary and unique keys (p.392) I pointed out that it is no longer necessary to enforce them through unique indexes, they could be enforced through non-unique indexes. This feature actually appeared in version 8.0, but has been made more useful in version 8.1 by the ability to defer (p.402) primary and unique key constraints that are enforced through non-unique indexes.

There are two problems that have recently come to my attention, though.

The first is a bug, number 1255474, which results in duplicate rows appearing under certain conditions when you use array inserts (through the dbms_sql pacakge, 3GL, or OCI) on tables where primary/unique keys are enforced through non-unique indexes. This bug is fixed in 8.1.7, and there are some backports to

The second is a side-effect of Oracle being too clever with imports. When you create a primary key constraint, Oracle will attempt to find an index that is suitable (and this may, or may not, be a unique one), and will only create a new index if there is no suitable pre-existing index. For example:

	create table tabX (n1 number, n2 number, n3 number);
	create index indX on tabX(n1, n2);
	alter table tabX add constraint pkX primary key (n1);

In this example, Oracle will use the pre-existing index indX to enforce the primary key constraint. By the way, if you put in a using index clause which does not agree with the existing index, then Oracle will not use the existing index, but will create a new one, e.g.

	alter table tabX add constraint pkX primary key (n1)
	using tablespace not_the_usual;

The side-effect appears on import.

Imagine you create a table with a primary key, then decide to add an index to that table that would have been suitable to enforce the primary key (perhaps it has the same columns in a different order with a couple of extra columns added to support a particular set of queries). Now export the table, and re-import it.

It is quite possible that Oracle will: import the table, create the indexes that have been defined explicitly, then alter the table to add the primary key constraint. So what happens ? Oracle discovers that there is already an index in place that will support the primary key constraint, so does not recreate the index that originally existed to enforce the constraint.

The performance of your system may suffer as a consequence; in the worst case, perhaps, the original index may have started with the primary key columns of a parent table so now you no longer have the critical PK/FK matching index pair (p.394) that you needed to avoid table locking.




Back to Book Index