Join order examples

The optimizer could select among several plans for this three-way join query:

select * 
from t1, t2, t3
where c11 = c21 
    and c12 = c31
    and c22 = 0
    and c32 = 100

Here are a few examples:

• Use c22 as a search argument on t2, join with t1 on c11, then with t3 on c31:

  (nl_join
      (i_scan i_c22 t2)
      (i_scan i_c11 t1)
      (i_scan i_c31 t3)
  )
  

• Use the search argument on t3, and the join order t3, t1, t2:

  (nl_join
      (i_scan i_c32 t3)
      (i_scan i_c12 t1)
      (i_scan i_c21 t2)
  )
  

• Do a full table scan of t2, if it is small and fits in cache, still using the join order t3, t1, t2:

  (nl_join
      (i_scan i_c32 t3)
      (i_scan i_c12 t1)
      (t_scan t2)
  )
  

• If t1 is very large, and t2 and t3 individually qualify a large part of t1, but together a very small part, this plan specifies a star join:

  (nl_join
      (i_scan i_c22 t2)
      (i_scan i_c32 t3)
      (i_scan i_c11_c12 t1)
  )
  

The join operators are generic in that they implement any of the outer joins, inner joins, and existence joins; the optimizer chooses the correct join semantics according to the query semantics.