Foreign key and index
614497Nov 3 2009 — edited Nov 11 2009Hi all,
My db is 10.2.0.3 RAC db with 2 nodes on MS window 2003 servers. My question is related to foreign keys and indexes. I have learned that we need to create an index for each foreign key in order to avoid table lock and improve performance. Here is my situation:
a parent table we call it parent (id is the primary key),
a look up table we call it lookup (id is primary key)
a child table wevcall it child ((parent_id, lookup_id) combination is the primary key on the child table). The parent.id and lookup.id are referenced as foreign keys as well. Obviously we have a unique key (for primary key) on parent_id, lookup_id in child table. My question is should we also create an index on parent_id and another index on lookup_id on the child table from best practice perspective? Our db is OLTP system.
Thanks a lot for your insights!
Shirley