RE: (Non)Unique Index Vs Unique Constraint

Thanks a lot for your quick response Mr.Jonathan and Mr.Dennis.

One more silly question...

What is the difference(pros & cons) between creating PK Vs (UK+NOT NULL) Vs (UK+check constraint with Not null condition)?

1)
drop table constraint_test;
create table constraint_test(c1 number,c2 varchar2(10)); alter table constraint_test add constraint ct_pk1 primary key(c1);

2)
drop table constraint_test;
create table constraint_test(c1 number,c2 varchar2(10)); alter table constraint_test modify(c1 number not null); alter table constraint_test add constraint ct_uk1 unique(c1);

3)
drop table constraint_test;
create table constraint_test(c1 number,c2 varchar2(10)); alter table constraint_test add constraint ct_ck1 check(c1 is not null); alter table constraint_test add constraint ct_uk1 unique(c1);

Thanks,
Jay

-----Original Message-----
Jonathan Lewis
Sent: Saturday, January 17, 2004 6:54 PM To: Multiple recipients of list ORACLE-L

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