RE: non unique Index and Primary Key on same column. Is it needed?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Aug 2004 01:17:14 -0400
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@xxxxxxxxxxxxx on behalf of Bobak, Mark
Sent: Tue 8/24/2004 12:39 AM
To: oracle-l@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx on behalf of nn20002@xxxxxxxxxxxx
Sent: Tue 8/24/2004 12:00 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
