Re: relationship between unique index and constraint

And just to mention the slightly less obvious:

    The optimzer may cost unique and PK constraints     that use non-unique indexes differently from those     that use unique indexes.

    The cost for using a deferrable or novalidate     constraint (either of which has to be on a     non-unique index) is higher than the cost of     using an immediate constraint (whether or not     the underlying index is unique or not).

So don't go changing immediate constraints to deferrable constraints without checking side-effects first; and don't be surprised if your execution paths change when you decide to novalidate a constraint to do a partitionexchange.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 20 April 2003 01:41

> Hi Arup,
>
> I might be mentioning the bleeden obvious here, but if the
constraint is
> deferrable, Oracle will use a non-unique index to police the
constraint. If
> a suitable non-unique index exists before a unique constraint is
created,
> Oracle will simply use it.
>
> Cheers
>
> Richard
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).