Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unique contraints vs. Unique indexes

Re: Unique contraints vs. Unique indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 13 Aug 2001 20:07:35 +0100
Message-ID: <997729491.19914.0.nnrp-07.9e984b29@news.demon.co.uk>

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>...

>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..."
Received on Mon Aug 13 2001 - 14:07:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US