Use Leading Hint to control join order

What is Leading Hint

The Leading Hint is a query optimization hint that instructs the optimizer to execute multi-table joins in a specified order rather than relying on the cost model for automatic selection.

The join execution order determines the size of intermediate result sets. For a three-table join A JOIN B JOIN C, the possible join orders are:

• (A JOIN B) JOIN C: Join A and B first, then join with C.

• (A JOIN C) JOIN B: Join A and C first, then join with B.

• A JOIN (B JOIN C): Join B and C first, then join with A.

Selecting the wrong join order may cause intermediate result sets to expand dramatically, leading to memory overflow or excessive execution time.

Syntax

Leading Hint controls the shape of the join tree through different syntax patterns. The core rule is simple: tables inside curly braces {} are joined as a group first, then joined with the outer tables. Without curly braces, tables are joined sequentially from left to right. Quick reference for the three tree structures:

-- Left-deep tree: ((t1 JOIN t2) JOIN t3) JOIN t4
SELECT /*+ LEADING(t1 t2 t3 t4) */
    ...
FROM t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id
JOIN t4 ON t3.id = t4.t3_id;

-- Right-deep tree: t2 and t3 join first, then the result joins t1
-- Use when: small dimension tables should merge before joining the large fact table
SELECT /*+ LEADING(t1 {t2 t3}) */
    ...
FROM t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id;

-- Bushy tree: two groups join independently, then the two results merge
-- Use when: tables form two natural clusters with separate join conditions
SELECT /*+ LEADING({t1 t2} {t3 t4}) */
    ...
FROM t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t3.id = t4.t3_id
JOIN t4 ON t2.id = t3.t2_id;

Examples

-- Without hint: the optimizer may choose orders JOIN users JOIN cities
-- With Leading Hint: join smaller tables first to reduce intermediate result sets
SELECT /*+ LEADING(users cities orders) */
    u.username, c.city_name, SUM(o.amount) AS total
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN cities c ON u.city_id = c.city_id
WHERE o.order_date >= '2024-01-01'
GROUP BY u.username, c.city_name;

-- Bushy tree: (orders JOIN order_items) JOIN (products JOIN categories)
-- Each group's join conditions are highly selective, producing small intermediate results
SELECT /*+ LEADING({orders order_items} {products categories}) */
    o.order_id, oi.quantity, p.product_name, c.category_name
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.order_date = '2024-06-01';

-- Reference subqueries by alias in the Leading Hint
SELECT /*+ LEADING(top_users o p) */
    top_users.username, p.product_name, o.amount
FROM (
    SELECT user_id, username FROM users WHERE vip_level >= 3
) top_users
JOIN orders o ON top_users.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

When to use Leading Hint

• Inaccurate statistics: The optimizer selects a suboptimal join order based on incorrect row count estimates.

• Data skew: Severe data skew on certain join keys causes a large gap between the actual and estimated intermediate result set sizes.

• Complex query tuning: For joins with more than 5 tables, the optimizer's limited search space may prevent it from finding the globally optimal solution.

• Stable execution plans: Business SQL requires a fixed execution plan to avoid performance fluctuations caused by statistics variations.

Verify Leading Hint effectiveness

Use EXPLAIN SHAPE PLAN to view the join tree structure and hint log, confirming whether the hint was adopted:

EXPLAIN SHAPE PLAN
SELECT /*+ LEADING(users cities orders) */
    u.username, c.city_name, SUM(o.amount)
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN cities c ON u.city_id = c.city_id
GROUP BY u.username, c.city_name;

-- Sample output (join tree + hint log):
-- PhysicalResultSink
-- --hashAgg[GLOBAL]
-- ----hashJoin[INNER_JOIN colocated] hashCondition=((o.user_id = u.user_id))
-- ------PhysicalOlapScan[orders]
-- ------hashJoin[INNER_JOIN broadcast] hashCondition=((u.city_id = c.city_id))
-- --------PhysicalOlapScan[users]
-- --------PhysicalOlapScan[cities]
--
-- Hint log:
-- Used: leading(users cities orders)
-- UnUsed:
-- SyntaxError:

The output has two parts: the join tree shows operator hierarchy with indentation, revealing the join order and distribution method (such as broadcast or colocated); the hint log shows how the hint was processed.

Best practices

• Table names in the Leading Hint must exactly match the table names or aliases in the FROM clause.

• Leading Hint supports inserting shuffle or broadcast between table names to specify the data distribution method at the same time, for example leading(t1 shuffle t2 broadcast t3). The optimizer prefers the specified distribution method, but automatically adjusts when a better strategy (such as Colocate Join) is available.

• As a general principle, place tables with smaller post-filter result sets earlier in the join order to reduce intermediate result expansion.

• First compare the default plan with the hint plan using EXPLAIN, then confirm the optimization effect through actual execution times.

• When the number of tables exceeds 10, consider splitting the query into multiple layered subqueries rather than relying on a single Leading Hint.