RE: Oracle join table index question: what is the ?right? way to create index?

From: Frederic Bidon <fbi_at_mobilix.dk>
Date: Wed, 26 Jan 2000 08:06:03 +0100
Message-ID: <2D3005375CAED31199D00008C784963F020911@ms02.mobilix.dk>

When you declare a PK, Oracle generates automtically an index.

Note that it is not very wise to let Oracle name and store the PK index without control : it will be called SYS_xxx and will certainly not be in the correct tablespace.

Better to use :
ALTER TABLE blah blah ADD CONSTRAINT name_of_my_constraint PRIMARY KEY(blah, blah) USING INDEX TABLESPACE blah blah STOAGE( blah blah).

so you can control the creation of your index objects (name of the index = name of the constraint), and your DBA can administrate the objects properly.

In a foreign key, the parent key should be a PK for its table.

My advice is to NEVER let Oracle do the job and ALWAYS control the creation of objects.

• naming will be consistent, so you can better understand your indexing scheme afterward than with SYS... objects.
• storage clauses can be harnessed, and thus, help in maintaining a good level of performances.

As for your different indexes,

• Your PK is a composite index, it can be used with best efficiency when both criteria are specified in a query.
• point 2 is irrelevant. Index created does exactly correspond to your PK definition.
• You can add non-unique indexes based on only one of the columns, if it fits some of your queries

As a general rule, there is no answer to a question like "shall I index this or that". What drives the creation of an index is the query. Study the queries and see which indexes they are likely to use, then check their execution plan to find out if they actually do.         

Frederic.

-----Original Message-----
From: gmei_at_my-deja.com [mailto:gmei_at_my-deja.com] Sent: 20. januar 2000 22:51
To: comp.databases.oracle.server_at_list.deja.com Subject: Oracle join table index question: what is the ?right? way to create index?

 Message from the Deja.com forum:
 comp.databases.oracle.server
 Your subscription is set to individual email delivery

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.

---

 Deja.com: Before you buy.
 http://www.deja.com/

• To modify or remove your subscription, go to http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
• Read this thread at http://www.deja.com/thread/%3C867vvo%24orc%241%40nnrp1.deja.com%3E

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Wed Jan 26 2000 - 01:06:03 CST