Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle join table index question: what is the “right” way to create index?
bejones wrote:
>
> the answer is # 1. Oracle always implicitly creates and index for primary
> key constraints.
>
Yes, but if he wants to run a query on Account_ID only, and it should use an index he will need to create a separate index on that column. (i.e. answer number 2, even if the reasoning given is misleading)
Of course Oracle uses all columns of a composite PK to create the index, but if you are not using all columns in a query, the index will only be available for the leading ones.
Maybe this helps to illustrate how composite indexes work:
table Phonebook:
lastname (PK)
firstname(PK)
phonenumber
Now go to the real world, if you are looking up a phone number in a phone book you are performing an index scan, if you have the full name, if you only have the last name you are still performing an index scan, but if I asked you to give me all the phone numbers of people with firstname Fred, you'd need to perform a full scan.
Same for Oracle, if you ask give me all the User_IDs for account_ID x.
Christian
> gmei_at_my-deja.com wrote in message <867vu1$oig$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 Fri Jan 21 2000 - 09:24:18 CST