Re: (Non)Unique Index Vs Unique Constraint

Depends what you want to achieve.

A non-unique index enforcing a unique
constraint allows the constraint to be
deferrable - so you could load some
'nearly unique' data against it and find the duplicates efficiently.

However, a non-unique index requires
one byte per entry more than the equivalent unique index - and some people are very fussy about making indexes as small as
possible.

As far as the optimizer is concerned, the unique constraint guarantees uniqueness
of data - which allows the 'single row'
optimisation to be used, and also results in an equality on the index to be costed at the 'unique index' cost, rather than the 'non unique index' cost. (But the cost thing changes again if the constraint is deferrable)

Bottom line - if you know that you never need to play silly games with the constraint, then a unique index is more efficient, and helps the optimizer more than a non-unique index.

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

Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland

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

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

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: Saturday, January 17, 2004 3:14 AM

> All,
>
> Please enlighten this Junior DBA.
>
> Which method is more efficient? When should I go for option (1)?
>
> 1)NON-UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
>
> 2)UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create UNIQUE index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
>
> Thanks in advance,
> Jay
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jay
> INET: jaysingh1_at_optonline.net
>
> 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).

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