Join order and flattening methods

A major factor in the choice of flattening method depends on the cost of the possible join orders. For example, in a join of t1, t2, and t3:

select * from t1, t2
where t1.c1 = t2.c1
and t2.c2 in (select c3 from t3)

If the cheapest join order is t1, t2, t3 or t2, t1, t3, a regular joinor or an existence join is used. However, if it is cheaper to perform the join with t3 as the outer table, say, t3, t1, t2, a unique reformat or duplicate elimination sort is used.

The resulting flattened join can include nested-loop joins or merge joins. When an existence join is used, merge joins can be performed only before the existence join.