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).
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-- 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
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 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
Re: (Non)Unique Index Vs Unique Constraint
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. In (1), your constraint is specifically named as ct_pk1. Oracle does the for you: a) create a unique index ct_pk for you in the default tablespace of the user. b) use this index to create the PK. c) makes this column not null if not already. In (2) you are doing the step (c) yourself, but you have a very important flexibility - you have explicitly made the column not null. Oracle creates a check constraint in the format SYS_Cn. It allows you to enable or disable it at will. Then you have added the unique constraint yourself, forcing Oracle to create a unique index. So, in effect you have created a primary key constraint, but still hav some flexibility in enabling/disabling the indivisual components. In (3) you have done the same as in (2), except that you have named the check constraint, which makes it easy in typing etc, as opposed to a name like SYS_C; but most important in case of migrations to a different database or rebuilds, where the name is preserved. The system generated names like SYS_C could change. All right, now that you can do it, the question is should you define a primary key this way? The answer is an emphatic NO. A primary key is not merely to enforce unqiueness and not-null values, but provides information about the contents of the table - an extension fo the logical design. In the US, every bank account holder has a Social Security Number (or a Taxpayer's Identification Number). So you can define a UK + Not Null constraint combination on the SSN column, which may be recommended, but the PK is the account_no. Perhaps as of now, only one person is allowed to open only account in the bank, so the SSN could potentilly be the PK, but that may not hold true in the future. An account is uniquely identified by the account_no, and that column should be defined as the PK; anyone else looking at the table also gets the information. Hope this helps. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 17, 2004 10:24 PM 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
(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).
