Jay
That is a good one. The question is: "How is the uniqueness constraint
being enforced when the index is nonunique?" Offhand I would have assumed
your constraint would have been rejected since the index is nonunique --
nope. Then I would have guessed the index would have been converted to a
unique index -- it isn't. Then I was skeptical whether the constraint was
really being enforced, but it is. The next question is how the uniqueness is
being enforced if the index is not unique.
Now, if you add some duplicate values to the table, then try to add the
constraint, you receive:
alter table index_test add constraint index_test_uk1 UNIQUE(c1)
*
ERROR at line 1:
ORA-02299: cannot validate (SILT.INDEX_TEST_UK1) - duplicate keys found
My guess, and this is only a guess, is that the nonunique index with a
unique constraint executes the code for a unique index. I ran an insert on
each version and the plan looks the same.
Of course, if you go around doing funny stuff like this, the person that
eventually takes over your job duties will probably really bless your name
each time they stumble onto something like a unique constraint on a
nonumique index.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Friday, January 16, 2004 9:14 PM
To: Multiple recipients of list ORACLE-L
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: DENNIS WILLIAMS
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).
