RE: indexes

From: Chris Griffith <Chris.Griffith_at_dstm.com>
Date: Thu, 11 May 2000 19:57:05 -0400
Message-Id: <10494.105436@fatcity.com>

I can think of a case where you wouldn't necessarily need a index on the FK:

Table Parent with column A with 20 rows in the table and PK on A. Table Child with columns A,B,C with 20 rows in the table all of the columns in a PK on the Child table. Column C has a FK referencing Parent.

There would be no need to index column C with its own index if the PK on the Child table prevents the table lock on the Parent table. I think (from what I've noticed in my databases) that the PK handles the table lock, but I'm not certain. Since there are few rows in the table you wouldn't need to index the C column unless the PK doesn't prevent the table lock on the Parent table.

I never have seen any definitive answer to this.

Chris Griffith
OCP DBA
Datastream Systems, Inc.

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

From: Vidya Kalyanaraman [mailto:kvidya13_at_hotmail.com] Sent: Thursday, May 11, 2000 5:36 PM
To: Multiple recipients of list ORACLE-L Subject: RE: indexes

I think if you have a composite PK of A,B and C, then if you want RI, then your FK should also be a composite key of all the three.

Moreover, it is better to create indexes on FK columns.... Correct me if I am wrong.
Thanks
Vidya

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 10 May 2000 22:27:42 -0800

Chris,

You hit the nail on the head! Does anyone know if this is true about indexes/PK/FK?

Val

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

Sent: Tuesday, May 09, 2000 5:32 PM
To: Multiple recipients of list ORACLE-L

Good question, I've wondered the same thing. Add to that question if you have a Primary key with 3 columns A,B,C in the PK and have a foreign key on column A,B will the PK index be used for A,B to prevent the table lock on the referenced table.

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

Sent: Tuesday, May 09, 2000 4:37 PM
To: Multiple recipients of list ORACLE-L

Is it safe to say that Oracle will use the composite index on A,B,C if A is a foreign key. What I'm getting at is that I don't need a separate index on column A which is a foreign key if I already have the composite index A,B,C

Thanks for all the help!
Val

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

Sent: Tuesday, May 09, 2000 12:35 PM
To: Multiple recipients of list ORACLE-L

Valerie,

I believe it can use the A column of the index, but not the C column, so depending on how selective A is, this may or may not be a good thing.

Gary

Gary Kirsh
Next Extent, Inc.

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

Sent: Tuesday, May 09, 2000 11:10 AM
To: Multiple recipients of list ORACLE-L

I know this issue has been discussed before but I can't find the answer in my list archives.

If you have a composite index on a table consisting of columns A,B,C in that order, will the index be used if I have a query/where clause on columns A and C?

I know that it will if I query on A alone or A,B or A,B,C but I can't find documentation about A and C (no B)

Thanks in advance!
Val

Valerie H. Webber
Management Systems Designers, Inc
Valerie.H.Webber_at_m1.irs.gov
704-569-1002 x107

--

Author: Chris Griffith

INET: Chris.Griffith_at_dstm.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_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).

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

--

Author: Vidya Kalyanaraman
INET: kvidya13_at_hotmail.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