The query can be flattened to an existence join:
select *
from t1, t2
where c11 = c21
and c21 > 100
and exists (select * from t3
where c31 != t1.c11)
The “!=” correlation can make the scan of t3 rather expensive. If the join order is t1, t2, the best place for t3 in the join order depends on whether the join of t1 and t2 increases or decreases the number of rows, and therefore, the number of times that the expensive table scan needs to be performed. If the optimizer fails to find the right join order for t3, the following abstract plan can be used when the join reduces the number of times that t3 must be scanned:
(g_join
(scan t1)
(scan t2)
(scan (table t3 (in (subq 1) ) ) )
)
If the join increases the number of times that t3 needs to be scanned, this abstract plan performs the scans of t3 before the join:
(g_join
(scan t1)
(scan (table t3 (in (subq 1) ) ) )
(scan t2)
)
