Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle join table index question: what is the “right” way to create index?

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

From: cbantzer <christian_bantzer_at_psmfc.org>
Date: Fri, 21 Jan 2000 07:24:18 -0800
Message-ID: <38887A22.508442DC@psmfc.org>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US