Mark D Powell wrote:
>>>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 --
Additionally, and I think this is true of all Oracle versions since 7:
Build a unique index and you'll find no entry in xxx_constraints. Build
a unique constraint and you get data dictionary entries in
xxx_constraints and xxx_indexes.
And then there's that little thing about deferring enforcement and
disabling (except FBIs). Can't be done with a unique index. So same
functionality with far less flexibility.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sun Jun 19 2005 - 12:00:25 CDT
