Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unique contraints vs. Unique indexes
Connor,
I believe that even if the index is non-unique, the optimizer can still appreciate that it is there to represent a unique/PK constraint, and therefore base its calculations on a predicate returning a single row.
Your point about finding duplicates quickly is
the best argument for the deferrable option, but
Interestingly, you can even create a PK constraint
as RELY DISABLE NOVALIDATE - and have a
primary key with no index. I haven't tested this
exhaustively as a way of driving the optimizer
path without having a large index - but it looks
an useful option.
-- Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html Connor McDonald wrote in message <3B781ED6.EB4_at_yahoo.com>...Received on Mon Aug 13 2001 - 14:07:35 CDT
>James Williams wrote:
>>
>> I am about to build a data warehouse that will eventually be 1 TB.
>>
>> A PK's are going to have non_unique indexes and so that they can be
>> deferrable.
>>
>> What are the heavy hitters opinions on this?
>
>A non-unique index is less "attractive" to the optimiser than a unique
>one, but deferrable primary keys are very nice when removing duplicates
>(ie cleaning up your data)...
>
>... I'd look at using deferrable constraints where you think they will
>be appropriate rather then as a rule.
>
>hth
>connor
>--
>==============================
>Connor McDonald
>
>http://www.oracledba.co.uk
>
>"Some days you're the pigeon, some days you're the statue..."