Page History

...

The behavior of semantic validation is before optimizing the SQL, and the hebavior behavior when meets errors is to throw an exception.

...

Join Hint only affects the current query block, and does not affect the Join strategy in subquery and view. If the join hint really needs to be propagated into a view or subquery, we can discuss it in future,.

For example,

// view
create view view1 as select t1.* from t1 join on t2 on t1.a = t2.a

// BROADCAST will not be propagated into view1,
// and view1 will use the planner's default join strategy
select /*+ BROADCAST(t1) */ * from view1 join t3 on view1.a = t2.a

// the join in view1 will use the planner's default join strategy,
// and the join between view1 and t1 will use BROADCAST
select /*+ BROADCAST(t1) */ * from view1 join t1 on view1.a = t1.a

Support with table alias

Currently, join hint on alias is not supported, and user can create a view for this subquery with a specific view name and use this view name as an argument of the join hint.

For example,

// an exception that the v2 is not existent will be thrown 
select /*+ BROADCAST(v2) */ t1.a
from (select * from test1) t1 join test2
on t1.a = test2.a

// use the equivalent sql following
create view t1 as select * from test1;
select /*+ BROADCAST(v2) */ t1.a
from t1 join test2 on t1.a = test2.a

Conflict in one same hints

...