Re: Index and foreign key

"Olivier Crèvecoeur" <Olivier.Crevecoeur_at_ulg.ac.be> wrote in message news:<brsfea$1eom_at_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

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. Received on Fri Dec 26 2003 - 15:39:42 CET