You can use abstract plans to enforce the join order for merged views. This example creates a view. This view performs a join of t2 and t3:
create view v2 as select * from t2, t3 where c22 = c32
This query performs a join with the t2 in the view:
select * from t1, v2
where c11 = c21
and c22 = 0
This abstract plan specifies the join order t2, t1, t3:
(g_join
(scan (table t2 (in (view v2))))
(scan t1)
(scan (table t3 (in (view v2))))
)
This example joins with t3 in the view:
select * from t1, v2
where c11 = c31
and c32 = 100
This plan uses the join order t3, t1, t2:
(g_join
(scan (table t3 (in (view v2))))
(scan t1)
(scan (table t2 (in (view v2))))
)
This is an example where abstract plans can be used, if needed, to affect the join order for a query, when set forceplan cannot.
