RE: Primary Constraints and Indexes

From: <Babu.Nagarajan_at_Cummins.com>
Date: Thu, 11 Jan 2001 13:59:32 -0500
Message-Id: <10738.126419@fatcity.com>

wayne
i doubt whether oracle would allow you to create the index yyy on the same column list. it would come up with a error saying that such column list is already indexed.

Sachin Puri <sachin_at_siworldwide.com>@fatcity.com on 01/11/2001 03:00:20 AM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Subject: RE: Primary Constraints and Indexes

A Primary Key is a unique identification of a row in a database table. When a primary key is created an index is also created implicitly on that column.So u don't
need any explicit index on that column which has a primary key constraint. There can be only one Primary Key in a table however u can have a number of indexes in a table.(depends on the oracle release as in Oracle 7.x u can have 16 indexes in a table)
Explicit Indexes are generally used for fast data retrieval and perfomance enhancement.

Rgds,
Sachin Puri
Oracle Development and Administration
Solutions Infosystems

-----Original Message-----

Sent: Wednesday, January 10, 2001 9:16 PM To: Multiple recipients of list ORACLE-L

As a part-time dba, I am confused about the differences between Primary Keys and Indexes.

For example if I create a table "EMPLOYEES" and in the table creation statement I do something like:

     create table ...
        constraint XXX primary key (EMP_ID) using index ...

then later I create an index:

create index YYY on EMPLOYEE (EMP_ID) ... I know I have created two indexes (the first one system-named and implicitly created and the second name YYY and created explicitly) but do I really need the second index YYY? It seems to me the YYY index is not really needed? Can someone shed some light on this for me.

THANKS, Wayne
McGillWL_at_navair.navy.mil

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Wayne McGill
INET: McGillWL_at_navair.navy.mil

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_at_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: Sachin Puri
INET: sachin_at_siworldwide.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