3

Sorry for the simple question but i have asked a couple of people and received different answers.

I have a join table that is consisted of 2 columns: (aID,bID)

  • aID is a foreign key to table A and bID is a foreign key to table B

  • aID and bID together are the primary key of the join table.

Which of the below is the more efficient way to create indexes on the join table?

  1. Create a unique index on (aID,bID).
  2. Create a unique index on (aID,bID) and also create 2 separate indexes on aID and bID.
  3. Create separate indexes for aID and bID.
  4. None of the above (suggest your own).
StuartLC
  • 104,537
  • 17
  • 209
  • 285
Raz
  • 489
  • 3
  • 8
  • 17
  • 1
    use hadoop! (seriously, this is a silly question. too many unknowns. depends on what you are going to do with the tables) – MK. May 29 '15 at 05:07

1 Answers1

4

TL;DR

  • You don't need an index on (aID, bID) - since its the primary key, a unique index is already added.
  • You probably want a separate index on bID if isolated queries on bId are common in your usage e.g. filters like WHERE bID = 123
  • there may also be slight merit in considering an index only on aID (especially if bID isn't a narrow type).

But as with all indexing, you'll need to know how your data is being written, and accessed in order to optimize performance for all database consumers.

More Detail

Should I create a unique index on (aID,bID).

No, if there is already a primary key on (aID, bID), Oracle already has a unique index for this, so a further index here will be redundant.

Should I Create separate indexes for aID and bID.

There is a general rule of thumb to add an index to each foreign key column (independently), however, there may be a (very) few scenarios where this will be redundant or ineffective, e.g. where you you will never be filtering by the foreign key, or if the foreign key has no selectivity.

So drilling into each:

aID ?

Probably Not. Because the primary key is (aid, bid), there is already an index with aID as the first column, albeit not as dense as an index which would have been aid only.

bID ?

Probably, Yes. Although part of the unique index, bID is the second column, so the unique index isn't going to help if you have frequent queries where bID is used in isolation (i.e. on queries without aID present as well). But don't index bId if it isn't used in isolation from aID, and as per all indexes, if bID has low selectivity (e.g. only 2 distinct values for bID, each with a 50% distribution) then indexing is worthless.

Other considerations

Indexing requires intimate knowledge of how your database is utilized - new indices need to be carefully thought through, and existing indices need to analysed for redundancy (are they being utilized? are there similar indexes which will suffice?) and effectiveness (how much quicker is my query with the index?).

Also, the decision on whether to add many, or few, indexes will also depend on the bias of reading vs writing - more indexes adds I/O and disk overhead to writes, but this might not be a problem e.g. on a reporting server which gets rebuilt once per day.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • On a junction table - would it not be better to have composite unique key instead of two indexes? This way we have indexes + no duplicates? Not sure if composite index will work equally efficient as two separate indexes. – Witold Kaczurba Nov 19 '18 at 14:57
  • 1
    That's pretty much what I've tried to say in this answer, @WitoldKaczurba since the composite primary key (aID, bID). Oracle will automatically create have a unique index on this composite key. This same unique index will also work (relatively) well for queries on column `aID` in isolation. However, if there are queries on `bID` in isolation, e.g. `WHERE bID = xxx` then a separate secondary index is needed on `bID`, provided there's sufficient selectivity on `bID` for this to make sense. – StuartLC Nov 19 '18 at 15:04
  • 1
    Thanks @StuartLC. Upvoting. :). – Witold Kaczurba Nov 19 '18 at 15:17