I have two tables TACCOUNT & TCUSTOMER joined by one to many. I have
Account_sk in both tables which is used to join. When I create the
account_sk in TCUSTOMER as a foreign key, is there any added advantage
of having an index on the account_sk? Definitions of both tables are
below:

TCUSTOMER TABLE DEFINITION:

CREATE TABLE TCUSTOMER
(
CUSTOMER_SK NUMBER(10) NOT NULL,
CUSTOMER_ID CHAR(10 BYTE) NOT NULL,
EFFECTIVE_DT DATE,
EXPIRATION_DT DATE,
ACCOUNT_SK NUMBER(10)
);

ALTER TABLE TCUSTOMER ADD (
FOREIGN KEY (ACCOUNT_SK)
REFERENCES TACCOUNT (ACCOUNT_SK)
ON DELETE SET NULL);

CREATE INDEX IDX_ACCOUNT_SK ON TCUSTOMER
(ACCOUNT_SK);

ALTER TABLE TCUSTOMER ADD (
PRIMARY KEY (CUSTOMER_SK);


ACCOUNT TABLE DEFINITION:

CREATE TABLE TACCOUNT
(
EFFECTIVE_DT DATE,
EXPIRATION_DT DATE,
ACCOUNT_NM VARCHAR2(30 BYTE),
ACCOUNT_SK NUMBER(10) NOT NULL,
ACCOUNT_NO CHAR(10 BYTE)
)
;

CREATE UNIQUE INDEX IDX_TACCOUNT_ACCOUNT_NO ON TACCOUNT
(ACCOUNT_NO);


ALTER TABLE TACCOUNT ADD (
PRIMARY KEY (ACCOUNT_SK)


Many Thanks in Advance.

aravind.kanda (AT) gmail (DOT) com wrote:
By default, there is no index on the FK columns. However, it is normally
a good idea to index these columns. See the following:

http://asktom.oracle.com/pls/asktom/...D:292016138754
http://asktom.oracle.com/pls/asktom/...29201 6138754
http://asktom.oracle.com/pls/asktom/...:1156435031319

HTH,
Brian

--
================================================== =================

Brian Peasland
dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

Great pointers. Thanks for the quick response!

On May 29, 10:53 am, Brian Peasland <d... (AT) nospam (DOT) peasland.net> wrote: