(Non)Unique Index Vs Unique Constraint
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).
RE: (Non)Unique Index Vs Unique Constraint
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).
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" <[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).
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" <[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). -- 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 REMO
Re: (Non)Unique Index Vs Unique Constraint
t; 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). > -- > 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: Arup Nanda 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).
Re: (Non)Unique Index Vs Unique Constraint
(I'm resending my yesterday's post because it seems to have got lost) Dennis, You can have a unique constraint with a non-unique index. This is documented and expected behaviour. Actually, it's fairly easy for Oracle to enforce unique constraint using a a non-unique index. It just traverses to the key's first occurrence in index leaf block and then checks for the immediate next key in the same leaf block. If it is a different value, then we know our key is unique, because keys in index leaf blocks are always ordered and any equal values would be next to each other. But if the next key IS exactly the same as the first one, then we know that unique constraint is violated and an error will be returned. It gets a bit more complicated when our key doesn't have any more keys immediately next to it in a leaf block. In that case Oracle follows the "next leaf" pointer and reads in the next block in chain and checks for the key value there. And if we had lot's of completely empty leaf blocks next in chain, then Oracle has to read in every single one of them until it either finds a key value there or hits the last block, where the "next pointer" is zero. (If you wanted to say here that "hey, a b*-tree index can't have a completely empty block in it, because when the last row is deleted from a block, it is freed", then there comes an internal optimization into play. When the last row from an index block is deleted, the block is put in index segment freelist, but it isn't actually removed from b*-tree chain. It will be removed when the leaf block is reused by someone else, thus causing additional IO because leaf block prior and next to it have to be updated. This mechanism probably helps to deal better with rollbacks of large deletes or updates (updates actually mean deletes+inserts for an index key anyway). This behaviour can easily be revealed using a large delete, small insert and a treedump). I think that it is reasonable to make all indexes in your system non-unique and use unique constraints on them where needed. The reasoning behind it is that if you have some maintenence or conversion operations to do on a large table, which could have use for temporary non-uniqueness, then it's much cheaper to disable/reenable the constraint than drop and build the index. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, January 17, 2004 11:09 PM > 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-
Re: (Non)Unique Index Vs Unique Constraint
Add this to what Arup said: PK enables one to have References established in a schema. (Parent Child relationships i mean). That cannot be done just by having a unique and/or not null constraints set. You will get ORA-02270: no matching unique or primary key for this column-list HTH GovindanK On Sat, 17 Jan 2004 20:49:25 -0800, "Arup Nanda" <[EMAIL PROTECTED]> said: > Jay, > > Remember, both UK and PK are enforced by unique indexes. The important > difference between them is a null value is allowed in UK, not in PK. > -- http://www.fastmail.fm - Access your email from home and the web -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GovindanK 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).
RE: (Non)Unique Index Vs Unique Constraint
Govindan, You can create FK relationship(Parent-child) by using UNIQUE constraint.. Correct me if I am wrong. -Original Message- Sent: Thursday, January 22, 2004 6:37 PM To: Multiple recipients of list ORACLE-L; Multiple recipients of list ORACLE-L Cc: [EMAIL PROTECTED] Add this to what Arup said: PK enables one to have References established in a schema. (Parent Child relationships i mean). That cannot be done just by having a unique and/or not null constraints set. You will get ORA-02270: no matching unique or primary key for this column-list HTH GovindanK On Sat, 17 Jan 2004 20:49:25 -0800, "Arup Nanda" <[EMAIL PROTECTED]> said: > Jay, > > Remember, both UK and PK are enforced by unique indexes. The important > difference between them is a null value is allowed in UK, not in PK. > -- http://www.fastmail.fm - Access your email from home and the web -- 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).
