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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
