Re: Oracle Warehouse Builder 9.2.0.2.8 and Workflow 2.6.2 on linux
Hi, I found Workflow 2.6.2 on the 9.2.0.1 Database cdrom set. You get to it by picking Enterprise Edition and then custom. -moi __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ir Forex 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).
free performance tuning books @ Veritas
Hi! Oracle Performance Tuning 101 eBook (by Gaja, Kirti and John Kostelac) is available to download in PDF from http://www.veritas.com/offer?a_id=3805 Btw, veritas has a free SQL Server tuning book on their site as well. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Strange Query Result,... urgent please
Dear All, I need help on the following query, the following has been simplified to show you the exact problem. Due to this error, the sql query is giving unstable result which affects on production reporting in my company. I wonder what's the real problem? SQL SELECT COUNT(1) FROM 2 (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD 3 FROM IVC_DET IVC 4 GROUP BY SO 5 ); COUNT(1) -- 44087 SQL l 1 SELECT COUNT(1) FROM 2 (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD 3 FROM IVC_DET IVC 4 GROUP BY SO 5* ) SQL / COUNT(1) -- 44085 From the above query nobody has changed any record in my production database but the result is unstable. After I remove group function it becomes stable as below. SQL SELECT COUNT(1) FROM 2 (SELECT SO 3 FROM IVC_DET IVC 4 GROUP BY SO 5 ); COUNT(1) -- 50770 SQL / COUNT(1) -- 50770 Now my opinion is the sort process has been ruined. I checked alert.log but I found no entries regarding this problem. So I check the sort ratio through performance manager and it shows almost 100% hit ratio. Well, just in case, next I checked on my Temporary tablespace and it still has a lot of room to do sorts. Anyone has experience this before? Please give me some light, ideas or suggestion to correct this, since I have a lot of very complex queries. I feel like a bomb is ticking for other complex queries. Thank you all in advance. Regards, Wendry. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wendry 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: Strange Query Result,... urgent please
Hi! Use SET TRANSACTION READ ONLY before your run the queries and run them again (without any commits or rollbacks in the middle). That way you can be sure that any hidden changes to data won't be reflected. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 17, 2004 3:54 PM Dear All, I need help on the following query, the following has been simplified to show you the exact problem. Due to this error, the sql query is giving unstable result which affects on production reporting in my company. I wonder what's the real problem? SQL SELECT COUNT(1) FROM 2 (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD 3 FROM IVC_DET IVC 4 GROUP BY SO 5 ); COUNT(1) -- 44087 SQL l 1 SELECT COUNT(1) FROM 2 (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD 3 FROM IVC_DET IVC 4 GROUP BY SO 5* ) SQL / COUNT(1) -- 44085 From the above query nobody has changed any record in my production database but the result is unstable. After I remove group function it becomes stable as below. SQL SELECT COUNT(1) FROM 2 (SELECT SO 3 FROM IVC_DET IVC 4 GROUP BY SO 5 ); COUNT(1) -- 50770 SQL / COUNT(1) -- 50770 Now my opinion is the sort process has been ruined. I checked alert.log but I found no entries regarding this problem. So I check the sort ratio through performance manager and it shows almost 100% hit ratio. Well, just in case, next I checked on my Temporary tablespace and it still has a lot of room to do sorts. Anyone has experience this before? Please give me some light, ideas or suggestion to correct this, since I have a lot of very complex queries. I feel like a bomb is ticking for other complex queries. Thank you all in advance. Regards, Wendry. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wendry 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: Tanel Poder 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).
any student versions of 'business objects' ab initio or informatica?
Something affordable. These are hot right now and I'd like to learn how to use them. I cant find anything on the web.
Re: any student versions of 'business objects' ab initio or
Title: Re: any student versions of 'business objects' ab initio or informatica? Probably best to contact them directly? I suspect that Ab Initio in particular will be out of reach for this purpose. My understanding is that they choose their customers based on their capability to deliver a data warehouse succesfully. Just a rumor, but it makes a certain amount of sense if you want to streamline your post-sales support costs. I understand that they are more selective of potential customers than the prospective customers are of ETL tools, actually deciding not to sell to certain prospects. Talk about business intelligence! If this is true, then I would suspect that student discounts are not an option. Of course, it might just be a rumor... on 1/17/04 9:44 AM, Ryan at [EMAIL PROTECTED] wrote: Something affordable. These are hot right now and I'd like to learn how to use them. I cant find anything on the web.
Re: any student versions of 'business objects' ab initio or
Title: Re: any student versions of 'business objects' ab initio or informatica? does business objects = crystal reports? anyone can use crystal reports? why are employers picky about people with business objects knowledge? https://secure.businessobjects.com/default.asp - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Saturday, January 17, 2004 1:04 PM Subject: Re: any student versions of 'business objects' ab initio or Probably best to contact them directly?I suspect that Ab Initio in particular will be out of reach for this purpose. My understanding is that they choose their customers based on their capability to deliver a data warehouse succesfully. Just a rumor, but it makes a certain amount of sense if you want to streamline your post-sales support costs. I understand that they are more selective of potential customers than the prospective customers are of ETL tools, actually deciding not to sell to certain prospects.Talk about business intelligence!If this is true, then I would suspect that student discounts are not an option. Of course, it might just be a rumor...on 1/17/04 9:44 AM, Ryan at [EMAIL PROTECTED] wrote: Something affordable. These are hot right now and I'd like to learn how to use them. I cant find anything on the web.
RE: any student versions of 'business objects' ab initio or infor
Ryan - Some more thoughts for your consideration. 1. Consider studying Data Warehousing. These tools are deliberately made easy to use, so shouldn't take much effort, but understanding how to design a star schema will make data more accessible to any tool. A good start can be had at http://www.ralphkimball.com http://www.ralphkimball.com and http://www.billinmon.com http://www.billinmon.com 2. In spite of the versatility of these tools, sometimes they can't do everything, particularly in the ETL phase. Consider learning a tool like perl to fill the gap. 3. There are ways to learn about a tool without operating it. Find a group like this dedicated to the tool. Try to locate user groups. See if you can get a manual. 4. Once you've learned quite a bit about the tool, contact the local sales rep for these tools and describe your desires. Use your winning charm. Let them know you are sincere to have learned what you are able. They may have some creative suggestions for you. Maybe let you sit in the back of a training class, or slip you an old manual they are ready to toss. Offer volunteer labor. It is in their best interest to have trained technical people available Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Saturday, January 17, 2004 10:44 AM To: Multiple recipients of list ORACLE-L informatica? Something affordable. These are hot right now and I'd like to learn how to use them. I cant find anything on the web. -- 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
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: Rename tablespace in 9I
No. It is coming in 10g (from Oracle World presentations I attended last year). - Kirti --- [EMAIL PROTECTED] wrote: Is there anyway to rename tablespace in Oracle 9202. Thanks __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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 to be
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;