Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
join_order, join_prefix, and join_suffix.Type | Scope of Application | Location Constraint | Use Cases |
join_order | fragment of the full table join order | intermediate position in the plan tree | Specify the path for multi-table joins |
join_prefix | Enforce left-deep tree order for prefix tables | leftmost end of the plan tree | Prioritize the processing of dimension tables |
join_suffix | Enforce right-deep tree order for suffix tables | rightmost end of the plan tree | Delaying large table joins |
SELECT /*+join_order(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a;
| physical_plan-> Projection: a a (rows=*)-> Inner Hash Join (a = a) (rows=*)-> Tdstore Scan Text: test.t1 Projections: a (rows=*)-> Tdstore Scan Text: test.t2 Projections: a (rows=*)
t2 is the build side and t1 is the probe side, which is consistent with the MySQL execution plan.SELECT /*+join_order(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t2.a=t3.a;
-> Inner Hash Join (a = a) (rows=*)-> Tdstore Scan Text: test.t1 Projections: a (rows=*)-> Tdstore Scan Text: test.t2 Projections: a (rows=*)
t3 table within the plan is undetermined and will be decided by the optimizer.SELECT /*+join_prefix(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t2.a=t3.a;
| physical_plan-> Projection: a a a (rows=*)-> Inner Hash Join (a = a) (rows=*)-> Tdstore Scan Text: test.t3 Projections: a (rows=*)-> Inner Hash Join (a = a) (rows=*)-> Tdstore Scan Text: test.t1 Projections: a (rows=*)-> Tdstore Scan Text: test.t2 Projections: a (rows=*)
t1 and t2 is specified and fixed at the leftmost position of the plan.SELECT /*+join_suffix(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t2.a=t3.a;
| physical_plan-> Projection: a a a (rows=*)-> Inner Hash Join (a = a) (rows=*)-> Tdstore Scan Text: test.t1 Projections: a (rows=*)-> Inner Hash Join (a = a) (rows=*)-> Tdstore Scan Text: test.t2 Projections: a (rows=*)-> Tdstore Scan Text: test.t3 Projections: a (rows=*)
t1 and t2 is specified and fixed to the rightmost position of the plan.SELECT /*+join_prefix(t2,t1) join_order(t3,t5,t4)*/ * FROM t1 JOIN t2 ON t1.a=t2.aJOIN t3 ON t2.a=t3.aJOIN t4 ON t3.a=t4.aJOIN t5 ON t4.a=t5.a;
SELECT /*+join_order(t2,t1) join_order(t1,t2)*/ * FROM t1 JOIN t2 ON t1.a=t2.a;
SELECT /*+join_order(t2,t1)*/ * FROM t1,t2;
피드백