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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/19/02 01:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [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.com
--
Author:
INET: [EMAIL PROTECTED]
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: [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).
