如何人工控制 CTE 的展开与物化？

SQL 中包含 CTE（common table expression）且 CTE 与其他表进行关联，优化器在选择执行计划时，会根据代价来确定是否对 CTE 进行展开或者物化。本文介绍人工控制 CTE 的展开与物化的方法。

适用版本

OceanBase 数据库 V3.x 版本。

通过 Hint 控制

CTE 的展开或物化可以由 SQL Hint 来控制。

• INLINE：展开
• MATERIALIZE：物化

SQL 示例

WITH TMP_TB( SELECT ... FROM TB1, TB2 WHERE ....)
SELECT ... FROM TB3,TMP_TB WHERE ...
UNION ALL
SELECT ... FROM TB3,TMP_TB WHERE ...

Hint 示例

• 强制展开 CTE

  WITH TMP_TB( SELECT /*+ INLINE */... FROM TB1, TB2 WHERE ....)
  SELECT ... FROM TB3,TMP_TB WHERE ...
  UNION ALL
  SELECT ... FROM TB3,TMP_TB WHERE ...
  

• 强制物化 CTE

  WITH TMP_TB( SELECT /*+ MATERIALIZE */... FROM TB1, TB2 WHERE ....)
  SELECT ... FROM TB3,TMP_TB WHERE ...
  UNION ALL
  SELECT ... FROM TB3,TMP_TB WHERE ...
  

通过配置项控制

从 V3.2 版本开始，OceanBase 数据库提供了租户级配置项来控制 CTE 的优化策略，如下表所示。

默认的优化策略为 _with_subquery=0，即优化器自行决定是否需要物化或者展开 CTE。

_with_subquery 是租户级配置项，如果在 SYS 租户中设置租户级配置项，需要指定 TENANT。

示例如下：

alter system set _with_subquery=1 tenant ob_mysql;