Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g1K05pE20360 for ; Tue, 19 Feb 2002 19:05:51 -0500 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id QAA38094; Tue, 19 Feb 2002 16:02:50 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b70/bab) via UUCP id 00413B62; Tue, 19 Feb 2002 15:48:24 -0800 Message-ID: Date: Tue, 19 Feb 2002 15:48:24 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Jared.Still@radisys.com Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Jared.Still@radisys.com Subject: Re: add index to a unique-constrained column--how come? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 70; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Roy, I think that the fine manual is saying is that if you need an index for performance reasons, create it explicitly, not implicitly as you would be by creating a unique constraint. Jared "Pardee, Roy E" Sent by: root@fatcity.com 02/19/02 01:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L cc: Subject: add index to a unique-constrained column--how come? The Oracle9i Database Administrator's Guide says: > Creating a Unique Index Explicitly > > Indexes can be unique or nonunique. Unique indexes guarantee that > no two rows of a table have duplicate values in the key column > (or columns). Nonunique indexes do not impose this restriction on > the column values. > > Use the CREATE UNIQUE INDEX statement to create a unique index. > The following example creates a unique index: > > CREATE UNIQUE INDEX dept_unique_index ON dept (dname) > TABLESPACE indx; > > Alternatively, you can define UNIQUE integrity constraints on the > desired columns. Oracle enforces UNIQUE integrity constraints by > automatically defining a unique index on the unique key. This is > discussed in the following section. However, it is advisable that > any index that exists for query performance, including unique > indexes, be created explicitly (See it at http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/in dexes.htm#10069) If there's already an index there for the constraint, why do we want an additional one? Does it take up space? Will the implicit (is that the right word?) index not be used in queries if you don't also create an explicit one? Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: roy.e.pardee@lmco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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.com -- Author: INET: Jared.Still@radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).