Great pointers. Thanks for the quick response!
On May 29, 10:53 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> aravind.ka..._at_gmail.com wrote:
> > 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.
>
> 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/f?p=100:11:0::::P11_QUESTION_ID:2...http://asktom.oracle.com/pls/asktom/f?p=100:11:29255896742093::::P11_...http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://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 fromhttp://www.teranews.com
Received on Tue May 29 2007 - 10:15:07 CDT
