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: Emmanuel HUMBLOT <e_humblot_at_wanadoo.fr>
Date: 21 Jan 2000 09:32:49 GMT
Message-ID: <01bf63fb$43b5eb00$3601017e@EHM.cirra.fr>


> 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

Original text of this message

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