If there is already an index on (PARENT_ID, LOOKUP_ID) in the child table, there would generally be no need to create a separate index on PARENT_ID. You generally don't need to have two indexes with the same leading columns. You would, however, almost certainly want to create a separate index on LOOKUP_ID.

Justin

That sentence can be viewed in two different ways (a) you've been told (or read) that you're supposed to create these indexes, or (b) you've discovered that for your system it's necessary.

As a general principle, you do not need to create "foreign key indexes" just because you have declared a foreign key constraint. (If it were necessary, Oracle would probably create the index automatically just as it does for primary key constraints). However, if you expect to delete parent rows, or update parent keys, then a "foreign key index" will avoid the need to lock the child table; and, fairly often, the "foreign key index" may be a useful index in its own right for performance reasons would be an index that you would create even if you had't declared the constraint.

This actually sounds more like an "intersection table", representing a many-to-many association between lookup and parent.

You need three indexes to enforce the uniqueness of the primary keys (although the indexes need not be unique, and need only start with the primary key columns, so you will have:

parent(id)
    lookup(id)
    child(id_parent, id_lookup)   or child(id_lookup, id_parent)

(Or indexes starting with these columns).

It might be perfectly reasonable (for your application) to have both the indexes on the child as this might allow you to execute some queries that join from lookup through child to parent without visiting the child table working in either direction.

It might be necessary (for your application) to have at least

a) (id_parent, id_lookup) and (id_lookup)
or
b) (id_parent) and (id_lookup, id_parent)

(or indexes starting that way) to ensure that you have covered both foreign keys and the primary key. It is entirely possible that having just (id_parent, id_lookup) or just (id_lookup, id_parent) will work for you because you know that you won't run the risk of updating one of the two outer tables in a way that causes a locking problem, and the other index would simply be surplus to requirements.

Bottom line:
Don't create "foreign key indexes" unless you actually need them
"Primary key" and "foreign" key indexes only have to start with the relevant columns.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {code} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

I'd certainly consider it. Whether or not I decided in favour not would depend on several factors:

It will be efficient going one way across the intersection, how efficient will the secondary index be if I need to go the other way (answer varies with version of Oracle)
how many non-key columns are there in the intersecton table
how many non-key columns should go into the overflow if there were more than four or five small columns
If it's a question of changing an existing structure, how much application code has to be checked for storing rowids rather than urowids

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {code} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

Thanks,

But what do you mean by "no other columns in overflow"?

Yes, although you might find a reason in some cases to include extra copies at the end of the index to allow some queries to avoid going to the primary key index to pick them up (but that's just the old trick of copying extra columns from the table into an index to avoid visiting the table).

In 10g, the secondary index will be a little smaller than in earlier versions because Oracle added an enhancement that means it doesn't have to duplicate primary key columns into the "urowid" portion of the index entry if they already appear in the definition of the secondary index. In fact, I have some sample code on my laptop dated Oct 2004 (five years old) that demonstrates this point and suggests that it makes IOTs a particularly interesting option for intersection tables.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {code} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

"no other columns in overflow" ? An additional requirement from queries from another side is needed, that they are interested in the extra column in their access path. Adding that to the additional overflow column to the secondary index talks about the index organized table overflow columns. The overflow word there has actually nothing to do with additional index.

The original child "intersection table" has no columns for the overflow. A question about overflow columns was answered by Jonathan. Even thou I did not ask that yet. Thank you for that. Funny, I will be talking about the issue in Ougf seminar today. You can find a "intersection table" structure in E. F. Codd paper from 1970. There is described a file H Segment Commit having columns (part#,project#,quantity committed). Column "quantity committed" might be considered to be added to overflow.

Earlier i wrote that the additional index should be unique. Unique when it contains all columns in the primary key. Additionally using compress 1 for the both indexes would be worth considering. Overflow for a secondary unique index. Should there be such a concept, to say, these columns support the unique constraint and the rest are overflow.

Edited by: Rafu on Nov 4, 2009 3:27 PM

Rafu: "Would it be worth considering to create the many to many link table as a index organized table (id_parent, id_lookup)."

I Agree with Rafu.
Used that construction several times.

An n-m relatoin needs two indexes, and on heap-table, one would overload the FK index to favour index-only lookup.
This would give you three segments: heap + pk (with all fields) + fk (probably overloaded with all fields) : everything stored 3 times.

But an IOT might be more efficient: no more heap required, just the two indexes, and FK index doesnt need to be overloaded.

The whole table would be PK (it can coutain some payload, but the it is not stricktly just an m-n relation anymore)
The PK would be the first index, the IOT segment with all the data in it.

the FK would be the other index,
and it would automatically contain the PK fields - hence index-only access, and no need to overload it with additional fields..

The relevant bonus feature of an IOT is that the rowid Is the PK.
The 2ndary index therefore contains the PK fields, hence no need to visit the table if you just need PK values.

(the row-guess on IOTs is, I think, a bit of a nuisance when the table has many ins/del, stale row-guesses just get in the way,
I would like to be able to just avoid row-guesses on busy indexes on IOTs)

I am very tempted to whip up a demo for n-m.