Index and foreign key

[Olivier Crèvecoeur]

Hello,

Excuse me for my poor english.
I would kike know if create index on the foreign key it's necessary or if
Oracle, are optimized for using foreign key whithout index.

Best regards

Olivier

[Mark C. Stock]

Re: Index and foreign key

depends on the version, depends on if you're concerned with performance or
concurrency

indexes on FKs can help join performance

until some release of 9i (don't remember which -- it might even be 10g, but
i know it's been discussed in these forums before) indexes on FKs were
necessary to minimize locks when a PK value was updated (but not necessarily
when other columns in the PK records where updated)

-- mcs


"Olivier Crèvecoeur" <Olivier.Crevec oeur@ulg.ac.bew rote in message
news:brsfea$1eo m@aix4.segi.ulg .ac.be...
| Hello,
|
| Excuse me for my poor english.
| I would kike know if create index on the foreign key it's necessary or if
| Oracle, are optimized for using foreign key whithout index.
|
| Best regards
|
| Olivier
|
|

[VC]

Re: Index and foreign key

Hello Olivier,

Unindexed foreign keys can cause a dead-lock during concurrent primary keys
updates/deletes under any current Oracle version including 9i.
I'd be very much impressed if this problem were fixed in 10g...

So if you anticipate updates/deletes to the primary key, the foreign keys
have to be indexed.

Also, an unindexed foreign key is a performance issue if:
-- you have an 'on delete cascade';
-- you query from the parent table to the child table;

Rgds.

VC



"Olivier Crèvecoeur" <Olivier.Crevec oeur@ulg.ac.bew rote in message
news:brsfea$1eo m@aix4.segi.ulg .ac.be...

Hello,
>
Excuse me for my poor english.
I would kike know if create index on the foreign key it's necessary or if
Oracle, are optimized for using foreign key whithout index.
>
Best regards
>
Olivier
>
>

[NicK]

Re: Index and foreign key

Hey VC!

If you check out the Oracle press materials on Oracle 9i, it explicitly
states that dead-locks are checked for and prevented. I guess they made
fools out of themselves by point out at something they forgot to
implement :-)

Either way, I hope MySQL, DB/2 and Oracle continue to remain in business
and keep pushing each other to their limits. MySQL because its included
for free with Linux, DB/2 because it maintains some degree of
compatibility with Oracle, and Oracle because we've been spending so
much time and effort on it.

Anyway, I'd better get going.

Cheers,
NicK


VC wrote:

Hello Olivier,
>
Unindexed foreign keys can cause a dead-lock during concurrent primary keys
updates/deletes under any current Oracle version including 9i.
I'd be very much impressed if this problem were fixed in 10g...
>
So if you anticipate updates/deletes to the primary key, the foreign keys
have to be indexed.
>
Also, an unindexed foreign key is a performance issue if:
-- you have an 'on delete cascade';
-- you query from the parent table to the child table;
>
Rgds.
>
VC
>
>
>
"Olivier Crèvecoeur" <Olivier.Crevec oeur@ulg.ac.bew rote in message
news:brsfea$1eo m@aix4.segi.ulg .ac.be...
>

>>Hello,
>>
>>Excuse me for my poor english.
>>I would kike know if create index on the foreign key it's necessary or if
>>Oracle, are optimized for using foreign key whithout index.
>>
>>Best regards
>>
>>Olivier
>>
>>

>
>
>

[VC]

Re: Index and foreign key

Hello Nick,


"NicK" <nospam@emirate s.net.aewrote in message
news:bs0op0$hsh 8@news-dxb.emirates.ne t.ae...

Hey VC!
>
If you check out the Oracle press materials on Oracle 9i, it explicitly
states that dead-locks are checked for and prevented.

Well, Oracle misled you. I can post a sample scenario where Oracle 9i
dead-locks during primary key updates with unindexed foreign keys (if you
are interested).


VC

[Ryan Gaffuri]

Re: Index and foreign key

"Olivier Crèvecoeur" <Olivier.Crevec oeur@ulg.ac.bew rote in message news:<brsfea$1e om@aix4.segi.ul g.ac.be>...

Hello,
>
Excuse me for my poor english.
I would kike know if create index on the foreign key it's necessary or if
Oracle, are optimized for using foreign key whithout index.
>
Best regards
>
Olivier

if your foreign key is not the lead column(s) in an index, all DML on
the parent table locks ALL records in the child table. Oracle claims
to have 'fixed' this in 9i. That is an over-statement. That have
improved it slightly.

Foreign keys should almost always be indexed. The question is, 'in
what do I not index foreign keys'. The only case I have run across is
when you are using historical data(and in those cases, I typically
dont use keys).

When you have an 'insert heavy' table, indexes can impede performance.
So on archive tables, you generally dont want many if any indexes. So
in this case you may not want to index your foreign key. I generally
prefer to totally denormalize in these cases.

Im sure there are some other cases when you would not want to index
foreign keys, however, think to index first, then 'maybe' in some
cases not to index.