1

Learning Oracle basics, I learned that foreign keys should be indexed, thanks to this question.

However if I have a table with several foreign keys, should I create 1 index for each foreign key or 1 index for the entire table ?

For example, I have the table A(a,b,c,d), b and c being foreign keys on tables B and C.

Is the best solution :

CREATE INDEX index_tab ON A(b,c);

Or should I go with :

CREATE INDEX index_A_1 ON A(b);
CREATE INDEX index_A_2 ON A(c);
ThomasGth
  • 826
  • 7
  • 18
  • 3
    Without knowing more details about the expected usage patterns, the answer is going to be "it depends". From an [_"I need to index foreign keys to prevent unnecessary locking"_](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754) point of view though, it's probably best to start with one index per FK and refine things from there. – William Robertson Jan 25 '18 at 10:33
  • That's what I thought but I wanted to be sure. Thanks a lot William ! – ThomasGth Jan 25 '18 at 14:12

0 Answers0