Oracle join table index question: what is the “right” way to create index?

From: <gmei_at_my-deja.com>
Date: Thu, 20 Jan 2000 21:50:00 GMT
Message-ID: <867vu1$oig$1@nnrp1.deja.com>

Hi:

I have a question to create index on FKs in a join table.

As an example, say we have three tables:

UserTable:

User_ID (PK)
User_FirstName
User_LastName

....

AccountTable:
Account_ID (PK)
Account_Name
...

UserAccountTable:
User_ID (PK)(FK)
Account_ID (PK)(FK)
…

Are User_ID and Account_ID in UserAccountTable automatically indexed because of the composite PK in Oracle? Or only the leading column (User_ID in this case) is indexed?

Now say I want to index all FKs in the database. Which of the following is correct in terms of UserAccountTable?

1. I don’t need to index User_ID and Account_ID because they are indexed automatically due to PK
2. I need to index Account_ID separately because Oracle only indexes the leading column in composite PK.
3. I need to index both User_ID and Account_ID separately.

My understanding is I should do #2. I looked at a couple of books and couldn’t find the answer. Where can I find the “official” answer?

If you reply, please send a copy to zlmei_at_hotmail.com

Thanks in advance.

Guang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 15:50:00 CST