SCALAR SUBQUERY MATERIALIZE HINT

背景信息

MaxCompute支持标量子查询(Scalar SUBQUERY)，它的执行结果是一个标量值。通常情况下Scalar subquery是由MaxCompute系统内部转换成JOIN来执行的。在特定场景下，可以预先计算标量子查询的结果，将其作为常量参与后续运算，从而提升查询性能。

使用限制

• 并非所有标量子查询（Scalar Subquery）都支持预计算

  当子查询中包含非等值关联条件（如 >、<、!= 等）时，系统无法将其物化（materialize），而必须转换为 JOIN 执行。此时即使使用了 /*+ materialize */ Hint，也会被忽略，并输出警告信息。

  -- 这个flag允许系统输出warning
  SET odps.compiler.warning.disable=false;
  
  -- 子查询中有非等值的关联条件foo_t2.b > foo_t1.b，导致scalar subquery只能转join执行
  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));

  返回警告信息如下：

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

• Materialize Hint 会引入额外作业开销

  使用 materialize hint 后，系统会启动一个单独的Job来计算标量子查询的结果。由于额外的Job调度存在开销，建议通过实际测试评估该 hint 对性能的影响后再决定是否启用。

使用方法

在Scalar Subquery中通过 /*+ materialize */ 来指定先计算scalar subquery的值，且HINT必须紧贴于SUBQUERY对应的左括号之后书写。用法如下。

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

使用示例

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);

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

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