RE: non unique Index and Primary Key on same column. Is it needed?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 24 Aug 2004 01:17:14 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB23@bosmail00.bos.il.pqe>

Er, of course, that first sentence should read: "At primary key creation time, if an index w/ the appropriate column(s)=20 already exists, Oracle will utilize it, rather than creating a redundant =

index."

Also, while I'm on the subject, I'll also add a couple of other points: 1.) If you have a multi-column index, Oracle may also be able to = utilize
that for primary key enforcement. For example, if you created an index = on
(MACHINE_KEY,USABILITY_SETTINGS_KEY), that index may also be used for = the=20
primary key enforcement, because the leading edge matches the key. If = the
index was on (USABILITY_SETTINGS_KEY, MACHINE_KEY) that index could not = be
used to enforce a PK on MACHINE_KEY. =20

2.) I know I had one other point to make....but now it escapes me.... It's late and I need sleep. Hopefully a more alert person will fill in whatever blank I'm leaving behind....;-)

Good night,

-Mark

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

From:	oracle-l-bounce_at_freelists.org on behalf of Bobak, Mark
Sent:	Tue 8/24/2004 12:39 AM
To:	oracle-l_at_freelists.org; oracle-l_at_freelists.org
Cc:=09
Subject:	RE: non unique Index and Primary Key on same column. Is it =

needed?
Nagarajan,

At primary key creation time, if an appropriate column already exists, =
=3D

Oracle
will utilize it, rather than creating a redundant column.

In fact, note that a non-unique index may be used to enforce a primary or unique key constraint. The index need not be unique.

In cases where Oracle does generate an index automatically, you will =3D have
a name of the form 'SYS_Cxxxxxxx', where xxxxxx is a number.

Hope that helps,

-Mark

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

From:	oracle-l-bounce_at_freelists.org on behalf of nn20002_at_netscape.net
Sent:	Tue 8/24/2004 12:00 AM
To:	oracle-l_at_freelists.org
Cc:=3D09
Subject:	non unique Index and Primary Key on same column. Is it needed?

Hi ,

I saw a script creating a table and then an index on one of the column. =
=3D

But after that they alter the table to create an Primary Key on the same =
=3D

column.
Why is that needed. I am of the assumption that when you create a =3D primary key, the system automatically creates an index to enforce it. =
=3D

The same key could be used as an index also. Then why they create an =3D extra index.
example

CREATE TABLE MACHINE(

        MACHINE_KEY INTEGER  NOT NULL ,
        USABILITY_SETTINGS_KEY INTEGER  NULL ,
        MACHINE_ID VARCHAR2(256)  NULL ,
        NAME VARCHAR2(50)  NULL ,
        STATUS VARCHAR2(25)  NULL=3D20
     =3D20

);

   =3D20
CREATE INDEX MACHINE_INDEX ON MACHINE(
        MACHINE_KEY
);

   =3D20
ALTER TABLE MACHINE
    ADD PRIMARY KEY ( MACHINE_KEY );
     =3D20
I would appreciate if you could help me. Sorry if it is a silly =3D question.
Thanks
nagarajan

---

Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at =3D http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp

---

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

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Aug 24 2004 - 00:12:53 CDT