Re: Outcome of my research on OCP.

>> The same thing goes for unique indexes. There is no valid reason I can think of, in Oracle, to ever create a unique index. <<

I think the truth of Daniel's statement depends on the version of Oracle in question and the presence of declared PK and UK constraints on the indexed columns. There were a couple of bugs related to enforcement of the uniqueness of the data when a non-unique index was used to enforce PK constaints. Also in the absence of a PK or UK constraint the CBO costing was affected by the uniqueness or non-uniqueness of the index.

Then there is the issue of dealing with the basic design and existing DDL routines for your 'legacy' system. If you do not use deferred constraints then there is little reason to go to the effort of conversion of your unique indexes to non-unique indexes.

Still for new development on 9.2 and 10g Daniel is correct in that all indexes should probably be declared as being non-unique and PK and UK constraints fitted on top of these indexes where appropriate. I do believe that this is in fact what Oracle recommends in the DBA Administration manual though Oracle does not follow this recommendation in most of their examples. It will probably be years before non-unique indexes becomes the norm.

IMHO -- Mark D Powell -- Received on Sun Jun 19 2005 - 11:09:43 CDT