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?
> the answer is # 1. Oracle always implicitly creates and index for
primary
> key constraints.
Yes It does. BUT it will be useful in only two cases :
Let's take a similarity. Here's a list of people (Name, First Name, Salary (eg)) listed by alphabetical order od Name and First Name :
ABEL Jonas 1300 ADAMS Brian 1000 ADAMS John 1500 ADAMS William 1200 LEWIS Jim 3000 LEWIS Pat 3000 MARTIN Pete 1100 MARTIN Susan 3000 MILLER John 2500
The sorting by alphabetical order will help you find quickly a person with a given name and first name, or people with a given name.
But it WON'T help you find quickly people with a given first name : you need an additional indexation system.
So the correct answer is #2 ("I need to index Account_ID separately") in order to facilitate search for a given Account_ID, even if it is not exactly true that "Oracle only indexes the leading column in composite PK". It indexes a set of column taken as a whole. But it doesn't index every column as if it were separately indexed.
--
Regards
Emmanuel HUMBLOT e_humblot_at_wanadoo.fr
>
> 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 - 03:32:49 CST