"Olivier Crèvecoeur" <Ol****************@ulg.ac.be> wrote in message news:<br*********@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.