Specifying join order for queries using views

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.