Index on join and where

Question

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!

Modal

It doesn't make sense. The optimizer will push the WHERE conditions to the base tables and will ONLY read the rows with the "proper" years before it will do any joining. Do you have an index on "year" in both tables? THAT would help. – Victualer

Answer 1

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.

Snobbery

But isn't B table' s index enough? Is the A table index necessary? – Modal

@Modal . . . The index on A is useful if filtering by year is useful. – Snobbery

Sorry, what i wanted to say was: isn't an index over year enough on table A? Is it necessary the composite on A? – Modal

@Modal . . . Well, it is sufficient. By including the join columns in the index, the looking on the data page only needs to occur after the join rather than before. I admit, though, that I don't know if Oracle implements this optimization. – Snobbery

@GordonLinoff for the query OP asked, why the index on B can't be (year,id1,id2) as what u suggested for table A ? – Pictograph