Hints

Hints for SQL analyses provide optimization directives that guide query execution strategies in AWS Clean Rooms, enabling you to improve query performance and reduce compute costs. Hints suggest how the Spark analytics engine should generate its execution plan.

Syntax

SELECT /*+ hint_name(parameters), hint_name(parameters) */ column_list
FROM table_name;

Hints are embedded in SQL queries using comment-style syntax and must be placed directly after the SELECT keyword.

Supported hint types

AWS Clean Rooms supports two categories of hints: Join hints and Partitioning hints.

Combining multiple hints

You can specify multiple hints in a single query by separating them with commas:

-- Combine join and partitioning hints
SELECT /*+ BROADCAST(d), REPARTITION(8) */ e.name, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.id;

-- Multiple join hints
SELECT /*+ BROADCAST(s), MERGE(d) */ *
FROM employees e
JOIN students s ON e.id = s.id
JOIN departments d ON e.dept_id = d.id;

-- Hints within separate hint blocks within the same query
SELECT /*+ REPARTITION(100) */ /*+ COALESCE(500) */ /*+ REPARTITION_BY_RANGE(3, c) */ * FROM t;

Considerations and limitations