SCALAR SUBQUERY MATERIALIZE HINT-MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute allows you to use the Materialize Hint to precompute the result of a scalar subquery, which can improve query performance in specific scenarios. This topic describes the limits, usage, and examples of the Materialize Hint.

Background

MaxCompute supports Scalar subqueries , which return a scalar value. By default, a scalar subquery is internally converted to a JOIN for execution. In specific scenarios, the scalar subquery result can be precomputed and used as a constant in subsequent operations, which improves query performance.

Limits

Not all scalar subqueries support precomputation

If a subquery contains non-equijoin correlation conditions (such as >, <, or !=), the system cannot materialize the subquery and must convert it to a JOIN for execution. In this case, the /*+ materialize */ Hint is ignored and a warning is returned.

-- This flag allows the system to output warnings.
SET odps.compiler.warning.disable=false;

-- The subquery contains a non-equijoin correlation condition foo_t2.b > foo_t1.b, so the scalar subquery can only be converted to a JOIN for execution.
SELECT * FROM foo_t1 WHERE a = (/*+ materialize */ SELECT MIN(a) from foo_t2 WHERE (foo_t2.a = foo_t1.a) AND (foo_t2.b > foo_t1.b));

The following warning is returned:

WARNING:[1,37]  materialize hint does not work, the subquery is converted to join

The Materialize Hint introduces additional job scheduling overhead
After the materialize hint is used, the system starts a separate job to compute the scalar subquery result. Because the additional job scheduling incurs overhead, test and evaluate the performance impact of the hint before deciding whether to enable it.

Usage

Use /*+ materialize */ in a scalar subquery to precompute the subquery value. The hint must be placed immediately after the opening parenthesis of the subquery. Example:

SELECT a,
       (/*+ materialize */ SELECT b FROM foo_t2 WHERE foo_t2.a = foo_t1.a)
FROM foo_t1;

Examples

Sample data

CREATE TABLE foo_t1(a bigint, b bigint);
CREATE TABLE foo_t2(a bigint, b bigint);

INSERT OVERWRITE TABLE foo_t1 VALUES (1L, 1L), (20L, 2L);
INSERT OVERWRITE TABLE foo_t2 VALUES (1L, 1L), (10L, 10L);

Example 1: Basic usage and execution plan comparison

Without the hint, the scalar subquery is converted to a JOIN execution plan by default.
```
SELECT * FROM foo_t1 WHERE a = (SELECT MIN(a) FROM foo_t2);
```
Execution plan: Logview shows a two-table JOIN execution.
With the materialize hint, the subquery result is precomputed.
```
SELECT * FROM foo_t1 WHERE a = (/*+ materialize */ SELECT MIN(a) FROM foo_t2);
```
Execution plan: The system first runs a job to compute the scalar subquery result, and then starts a second job to compute the final result.

Example 2: Materializing scalar subqueries in script mode

In script mode, you can explicitly save the materialized scalar value to a variable and reuse it in subsequent queries to avoid repeated subquery execution:

@a := (/*+ materialize */ SELECT MIN(a) FROM foo_t2);

SELECT * FROM foo_t1 WHERE a >= @a AND a < 2 * @a;

Execution plan: Logview shows the following execution plan, where job_0 first computes the value of the variable @a, and subsequent computations reuse the result of job_0.