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

Because you declare (User_ID, Account_ID) the primary key of the table, it will be indexed automatically.

Some design tools will also create single column indexes on each of (User_ID) and (Account_ID) because they are foreign keys. The (User_ID) one should be dropped. The (Account_ID) one may be sufficient, but for reasons of I/O you may wish to drop it and create (Account_ID, User_ID) as a second index.

Having (account_Id, user_id) as well as (user_id,account_id) will allow you to join in either direction between User and Account without visiting the join table.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

gmei_at_my-deja.com wrote in message <867vvo$orc$1_at_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 - 16:06:43 CST