Is it always good to have index on all the foreign key constraints?
Hi Experts,
We have software product which runs on Oracle 11.2.0.1.0 and on 10g as well. Teh DB part of the product has approx 300 tables and 450 FK constraints
(One parent table has multiple child tables)
I have used Primary Key, Foreign ket many times and also understand the importance of having index on Foreign Key Constraints.
However in my DB environment where the no of FK are very huge but the no of FK with indexes are very small (less that 100), is it good to go for the indexes on all the FK.
Recently we faced a dead lock issue becuase of Table level locking since child table had not inxed on FK column.
I already reviewed below:
http://www.dba-oracle.com/t_foreign_key_indexing.htm
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_28.shtml
I just wanted to reconfirm that is it always good and safe to have index on all the FK column.
Regards
Arun