Index on join and where

sql oracle11g sqlperformance

Alex · Dec 21, 2016 · Viewed 10.9k times · Source

Given the next SQL statement:

Select * 
  from A join B
           on A.id1=B.id1 and 
              A.id2=B.id2
 where A.year=2016
   and B.year=2016

and knowing table A is much smaller than table B, so I need the database first to access A table by year, then join, then filter B table by year, my question is:

does it make sense to create an index over B like (id1,id2,year) for improve performance?

Many thanks!

Answer

For this query:

Select *
from A join
     B
     on A.id1 = B.id1 and A.id2 = B.id2
where A.year = 2016 and B.year = 2016;

I would suggest indexes on A(year, id1, id2) and B(id1, id2, year).

You might also write the query as:

Select *
from A join
     B
     on A.id1 = B.id1 and A.id2 = B.id2 and A.year = B.year
where A.year = 2016;

The answer to your question is "yes" and index on B is the right thing to do. In this version, the order of the columns in the index does not really matter.

Index on join and where

Answer

Related questions