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 that 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:

(nl_join
    (scan t2)
    (scan t1)
    (scan t3)
)

Since the table names are not ambiguous, the view qualification is not needed. However, the following abstract plan is also legal and has the same meaning:

(nl_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:

(join
    (scan t3)
    (scan t1)
    (scan t2)
)

This is an example where abstract plans can be used, if needed, to affect the join order for a query, when set forceplan cannot.