In previous releases, you forced join orders by using set forceplan on.
This example forces the join order so the lineitem table is outer to the orders table, so their join orders are switched in the from clause of the query:
set forceplan on 2> go 1> select count(*) from lineitem, orders where o_orderkey = l_orderkey 2> go
You can also force the join order with abstract plans. After you enable abstract plans, the query looks like this:
select count(*) from orders, lineitem where o_orderkey = l_orderkey plan "( nl_join ( t_scan orders ) ( t_scan lineitem ) ) ( prop orders ( parallel 1 ) ( prefetch 2 ) (lru ) ) ( prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) )
You can force the join order with abstract plans by switching their join order:
select count(*) from orders, lineitem where o_orderkey = l_orderkey plan "( nl_join ( t_scan lineitem) ( t_scan orders ) ) ( prop orders ( parallel 1 ) ( prefetch 2 ) (lru ) ) ( prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) )
