首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
如何为 CTE 公共表达式正确地添加 Hint
更新时间：2026-07-07 12:51
分享
适用版本：V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x内容类型：TechNote
通用表表达式（Common Table Expression，CTE）是一个命名的临时结果集，其作用范围仅限于当前语句。CTE 不实际作为对象存储，仅在查询执行期间被使用。与派生表不同，CTE 可以是自引用的，也可以在同一查询中多次引用。CTE 的主要优势在于提升 SQL 代码的可读性，使开发人员能够以更优雅、简洁的方式实现递归等复杂查询。
目前 OceanBase 数据库支持非递归 CTE 和递归 CTE：
OceaBase 数据库 MySQL 租户支持递归 CTE 和非递归 CTE。
-- 递归的 CTE MySQL [test]> with recursive T(n) as (select 1 union all select n+1 from t where n < 100) select sum(n) from t; +--------+ | sum(n) | +--------+ | 5050 | +--------+ 1 row in set (0.00 sec)-- 非递归的 CTE MySQL [test]> CREATE TABLE tbl1(col1 INT, col2 INT, col3 INT); Query OK, 0 rows affected (0.17 sec) MySQL [test]> INSERT INTO tbl1 VALUES(1,1,1),(2,2,2),(3,3,3); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 MySQL [test]> WITH w_tbl1 AS (SELECT * FROM tbl1) SELECT * FROM w_tbl1; +------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +------+------+------+ 3 rows in set (0.00 sec)OceanBase 数据库 Oracle 模式租户仅支持非递归 CTE。
-- 递归的 CTE obclient(SYS@oraclet)[SYS]> with recursive T(n) as (select 1 union all select n+1 from t where n < 100) select sum(n) from t; ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'T(n) as (select 1 union all select n+1 from t where n < 100) select sum(n) from ' at line 1obclient(SYS@oraclet)[SYS]> CREATE TABLE tbl1(col1 INT, col2 INT, col3 INT); Query OK, 0 rows affected (0.324 sec) obclient(SYS@oraclet)[SYS]> INSERT INTO tbl1 VALUES(1,1,1),(2,2,2),(3,3,3); Query OK, 3 rows affected (0.065 sec) Records: 3 Duplicates: 0 Warnings: 0 obclient(SYS@oraclet)[SYS]> WITH w_tbl1 AS (SELECT * FROM tbl1) SELECT * FROM w_tbl1; +------+------+------+ | COL1 | COL2 | COL3 | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +------+------+------+ 3 rows in set (0.005 sec)
Hint 是一种特殊的 SQL 语句注释，用于将指令传递给 OceanBase 数据库优化器（Optimizer）。通过 Hint 可以使优化器生成指定的执行计划。
本文主要介绍在包含 CTE 表达式的 SQL 语句中，如何正确地添加 Hint。如果 Hint 位置添加错误，OceanBase 数据库会选择直接忽略该 Hint 而不报错。
原理介绍
如果 OBServer 端无法识别 SQL 语句中的 Hint、Hint 语法不正确或 Hint 在当前上下文中不适用，OBServer 内核会选择直接忽略该 Hint 而不报错。因此，实际生效的 Hint 可以通过 EXPLAIN EXTENDED <业务 SQL 语句>; 或 DBMS_XPLAN.DISPLAY_CURSOR 输出结果中的 "Used Hint" 部分进行查看。
对于 CTE 表达式，Hint 不能直接添加在 WITH 关键字之后，否则该 Hint 将无效。
测试验证
准备测试表结构。
CREATE TABLE epub_accountbook ( id varchar(36) primary key, code varchar(255) DEFAULT NULL, name varchar(255) DEFAULT NULL ); CREATE TABLE fi_voucher ( id varchar(36) NOT NULL, creator varchar(64) DEFAULT NULL, description varchar(255) DEFAULT NULL, accbook varchar(36) DEFAULT NULL );验证在不同位置添加 Hint 是否生效。
将 Hint 直接添加在
WITH关键字后面（无效）：MySQL [test]> explain extended WITH /*+ materialize parallel(4) */ tmp_books AS (SELECT id FROM epub_accountbook e WHERE e.id IN ('1838983732300087318','2091172744342274053','2455462825193111554')) SELECT max( b.accbook ) AS accbook_id FROM fi_voucher b WHERE b.accbook IN( SELECT id FROM tmp_books ); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |18 | | | |1 |└─MERGE JOIN | |3 |18 | | | |2 | ├─TABLE GET |e |3 |14 | | | |3 | └─SORT | |1 |4 | | | |4 | └─TABLE FULL SCAN|b |1 |4 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_MAX(b.accbook(0x7f7c3f2688b0))(0x7f7c3f268e50)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(b.accbook(0x7f7c3f2688b0))(0x7f7c3f268e50)]) | | 1 - output([b.accbook(0x7f7c3f2688b0)]), filter(nil), rowset=16 | | equal_conds([b.accbook(0x7f7c3f2688b0) = e.id(0x7f7c3f238290)(0x7f7c3f274c50)]), other_conds(nil) | | merge_directions([ASC]) | | 2 - output([e.id(0x7f7c3f238290)]), filter(nil), rowset=16 | | access([e.id(0x7f7c3f238290)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([e.id(0x7f7c3f238290)]), range[1838983732300087318 ; 1838983732300087318], [2091172744342274053 ; 2091172744342274053], [2455462825193111554 | | ; 2455462825193111554], | | range_cond([e.id(0x7f7c3f238290) IN (cast('1838983732300087318'(0x7f7c3f361a40), VARCHAR(1048576))(0x7f7c3f361160), cast('2091172744342274053'(0x7f7c3f362920), | | VARCHAR(1048576))(0x7f7c3f362040), cast('2455462825193111554'(0x7f7c3f363800), VARCHAR(1048576))(0x7f7c3f362f20))(0x7f7c3f3609f0)(0x7f7c3f3600b0)]) | | 3 - output([b.accbook(0x7f7c3f2688b0)]), filter(nil), rowset=16 | | sort_keys([b.accbook(0x7f7c3f2688b0), ASC]) | | 4 - output([b.accbook(0x7f7c3f2688b0)]), filter([b.accbook(0x7f7c3f2688b0) IN (cast('1838983732300087318'(0x7f7c3f237980), VARCHAR(1048576))(0x7f7c3f26a3e0), | | cast('2091172744342274053'(0x7f7c3f237c50), VARCHAR(1048576))(0x7f7c3f26afc0), cast('2455462825193111554'(0x7f7c3f237f20), VARCHAR(1048576))(0x7f7c3f26bba0))(0x7f7c3f280cb0)(0x7f7c3f27b0b0)]), rowset=16 | | access([b.accbook(0x7f7c3f2688b0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([b.__pk_increment(0x7f7c3f26de20)]), range(MIN ; MAX)always true | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$BBD038BE > SEL$278BDE9F > SEL$07A8CF9F > SEL$E43F625F | | stmt_id:2, SEL$2 > SEL$E02A8D2F | | stmt_id:3, SEL$3 > SEL$AE07C0D1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$E43F625F" ("e"@"SEL$2" "b"@"SEL$1")) | | USE_MERGE(@"SEL$E43F625F" "b"@"SEL$1") | | FULL(@"SEL$E43F625F" "e"@"SEL$2") | | FULL(@"SEL$E43F625F" "b"@"SEL$1") | | INLINE(@"SEL$2") | | MERGE(@"SEL$E02A8D2F" > "SEL$3") | | UNNEST(@"SEL$AE07C0D1") | | SEMI_TO_INNER(@"SEL$BBD038BE" "VIEW1") | | PRED_DEDUCE(@"SEL$278BDE9F") | | MERGE(@"SEL$AE07C0D1" > "SEL$07A8CF9F") | | OPTIMIZER_FEATURES_ENABLE('4.2.5.7') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | e: | | table_rows:1 | | physical_range_rows:3 | | logical_range_rows:3 | | output_rows:3 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[epub_accountbook] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[DEFAULT] | | b: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[fi_voucher] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:1 | | estimation method:[DYNAMIC SAMPLING FULL] | | Plan Type: | | LOCAL | | Parameters: | | :0 => '1838983732300087318' | | :1 => '2091172744342274053' | | :2 => '2455462825193111554' | | Note: | | Degree of Parallelisim is 1 because of table property | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 90 rows in set (0.01 sec) MySQL [test]> explain extended WITH /*+ materialize() parallel(4) */ tmp_books AS (SELECT id FROM epub_accountbook e WHERE e.id IN('1838983732300087318','2091172744342274053','2455462825193111554')) SELECT max( b.accbook ) AS accbook_id FROM fi_voucher b WHERE b.accbook IN( SELECT id FROM tmp_books ); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |18 | | | |1 |└─MERGE JOIN | |3 |18 | | | |2 | ├─TABLE GET |e |3 |14 | | | |3 | └─SORT | |1 |4 | | | |4 | └─TABLE FULL SCAN|b |1 |4 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_MAX(b.accbook(0x7f83504688b0))(0x7f8350468e50)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(b.accbook(0x7f83504688b0))(0x7f8350468e50)]) | | 1 - output([b.accbook(0x7f83504688b0)]), filter(nil), rowset=16 | | equal_conds([b.accbook(0x7f83504688b0) = e.id(0x7f83504382a0)(0x7f8350474c50)]), other_conds(nil) | | merge_directions([ASC]) | | 2 - output([e.id(0x7f83504382a0)]), filter(nil), rowset=16 | | access([e.id(0x7f83504382a0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([e.id(0x7f83504382a0)]), range[1838983732300087318 ; 1838983732300087318], [2091172744342274053 ; 2091172744342274053], [2455462825193111554 | | ; 2455462825193111554], | | range_cond([e.id(0x7f83504382a0) IN (cast('1838983732300087318'(0x7f8350561a40), VARCHAR(1048576))(0x7f8350561160), cast('2091172744342274053'(0x7f8350562920), | | VARCHAR(1048576))(0x7f8350562040), cast('2455462825193111554'(0x7f8350563800), VARCHAR(1048576))(0x7f8350562f20))(0x7f83505609f0)(0x7f83505600b0)]) | | 3 - output([b.accbook(0x7f83504688b0)]), filter(nil), rowset=16 | | sort_keys([b.accbook(0x7f83504688b0), ASC]) | | 4 - output([b.accbook(0x7f83504688b0)]), filter([b.accbook(0x7f83504688b0) IN (cast('1838983732300087318'(0x7f8350437990), VARCHAR(1048576))(0x7f835046a3e0), | | cast('2091172744342274053'(0x7f8350437c60), VARCHAR(1048576))(0x7f835046afc0), cast('2455462825193111554'(0x7f8350437f30), VARCHAR(1048576))(0x7f835046bba0))(0x7f8350480cb0)(0x7f835047b0b0)]), rowset=16 | | access([b.accbook(0x7f83504688b0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([b.__pk_increment(0x7f835046de20)]), range(MIN ; MAX)always true | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$BBD038BE > SEL$278BDE9F > SEL$07A8CF9F > SEL$E43F625F | | stmt_id:2, SEL$2 > SEL$E02A8D2F | | stmt_id:3, SEL$3 > SEL$AE07C0D1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$E43F625F" ("e"@"SEL$2" "b"@"SEL$1")) | | USE_MERGE(@"SEL$E43F625F" "b"@"SEL$1") | | FULL(@"SEL$E43F625F" "e"@"SEL$2") | | FULL(@"SEL$E43F625F" "b"@"SEL$1") | | INLINE(@"SEL$2") | | MERGE(@"SEL$E02A8D2F" > "SEL$3") | | UNNEST(@"SEL$AE07C0D1") | | SEMI_TO_INNER(@"SEL$BBD038BE" "VIEW1") | | PRED_DEDUCE(@"SEL$278BDE9F") | | MERGE(@"SEL$AE07C0D1" > "SEL$07A8CF9F") | | OPTIMIZER_FEATURES_ENABLE('4.2.5.7') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | e: | | table_rows:1 | | physical_range_rows:3 | | logical_range_rows:3 | | output_rows:3 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[epub_accountbook] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[DEFAULT] | | b: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[fi_voucher] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:1 | | estimation method:[DYNAMIC SAMPLING FULL] | | Plan Type: | | LOCAL | | Parameters: | | :0 => '1838983732300087318' | | :1 => '2091172744342274053' | | :2 => '2455462825193111554' | | Note: | | Degree of Parallelisim is 1 because of table property | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 90 rows in set (0.01 sec)结论： 在
WITH关键字后直接添加的/*+ materialize parallel(4) */Hint 未被使用，Used Hint部分为空。将 Hint 添加在 CTE 表达式的括号内（生效）：
MySQL [test]> explain extended WITH tmp_books AS (SELECT /*+ materialize parallel(4) */ id FROM epub_accountbook e WHERE e.id IN ('1838983732300087318','2091172744342274053','2455462825193111554')) SELECT max( b.accbook ) AS accbook_id FROM fi_voucher b WHERE b.accbook IN ( SELECT id FROM tmp_books ); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =============================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------------------- | | |0 |TEMP TABLE TRANSFORMATION | |1 |7 | | | |1 |├─PX COORDINATOR | |0 |4 | | | |2 |│ └─EXCHANGE OUT DISTR |:EX10000 |0 |4 | | | |3 |│ └─TEMP TABLE INSERT |tmp_books|0 |4 | | | |4 |│ └─PX BLOCK ITERATOR | |3 |4 | | | |5 |│ └─TABLE GET |e |3 |4 | | | |6 |└─SCALAR GROUP BY | |1 |3 | | | |7 | └─PX COORDINATOR | |3 |3 | | | |8 | └─EXCHANGE OUT DISTR |:EX20001 |3 |3 | | | |9 | └─MERGE GROUP BY | |3 |2 | | | |10| └─SHARED HASH JOIN | |3 |2 | | | |11| ├─EXCHANGE IN DISTR | |1 |2 | | | |12| │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX20000 |1 |2 | | | |13| │ └─PX BLOCK ITERATOR | |1 |1 | | | |14| │ └─TABLE FULL SCAN |b |1 |1 | | | |15| └─TEMP TABLE ACCESS |tmp_books|3 |1 | | | =============================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_MAX(T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50))(0x7f782c1dc7b0)]), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | 2 - output(nil), filter(nil), rowset=16 | | dop=4 | | 3 - output(nil), filter(nil), rowset=16 | | 4 - output([e.id(0x7f782c038930)]), filter(nil), rowset=16 | | 5 - output([e.id(0x7f782c038930)]), filter(nil), rowset=16 | | access([e.id(0x7f782c038930)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([e.id(0x7f782c038930)]), range[1838983732300087318 ; 1838983732300087318], [2091172744342274053 ; 2091172744342274053], [2455462825193111554 | | ; 2455462825193111554], | | range_cond([e.id(0x7f782c038930) IN (cast('1838983732300087318'(0x7f7c45c21ef0), VARCHAR(1048576))(0x7f7c45c21610), cast('2091172744342274053'(0x7f7c45c22dd0), | | VARCHAR(1048576))(0x7f7c45c224f0), cast('2455462825193111554'(0x7f7c45c23cb0), VARCHAR(1048576))(0x7f7c45c233d0))(0x7f7c45c20ea0)(0x7f7c45c20560)]) | | 6 - output([T_FUN_MAX(T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50))(0x7f782c1dc7b0)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50))(0x7f782c1dc7b0)]) | | 7 - output([T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50)]), filter(nil), rowset=16 | | 8 - output([T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50)]), filter(nil), rowset=16 | | dop=4 | | 9 - output([T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(b.accbook(0x7f782c0688b0))(0x7f782c068e50)]) | | 10 - output([b.accbook(0x7f782c0688b0)]), filter(nil), rowset=16 | | equal_conds([b.accbook(0x7f782c0688b0) = tmp_books.id(0x7f782c068580)(0x7f782c06f410)]), other_conds(nil) | | 11 - output([b.accbook(0x7f782c0688b0)]), filter(nil), rowset=16 | | 12 - output([b.accbook(0x7f782c0688b0)]), filter(nil), rowset=16 | | dop=4 | | 13 - output([b.accbook(0x7f782c0688b0)]), filter(nil), rowset=16 | | 14 - output([b.accbook(0x7f782c0688b0)]), filter([b.accbook(0x7f782c0688b0) IN (cast('1838983732300087318'(0x7f782c038020), VARCHAR(1048576))(0x7f782c06a3e0), | | cast('2091172744342274053'(0x7f782c0382f0), VARCHAR(1048576))(0x7f782c06afc0), cast('2455462825193111554'(0x7f782c0385c0), VARCHAR(1048576))(0x7f782c06bba0))(0x7f782c079610)(0x7f782c0748b0)]), rowset=16 | | access([b.accbook(0x7f782c0688b0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([b.__pk_increment(0x7f782c06de20)]), range(MIN ; MAX)always true | | 15 - output([tmp_books.id(0x7f782c068580)]), filter(nil), rowset=16 | | access([tmp_books.id(0x7f782c068580)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | MATERIALIZE | | PARALLEL(4) | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$42E9F535 > SEL$3C119105 > SEL$548AE6DC > SEL$E1EFC3B3 | | stmt_id:2, SEL$2 | | stmt_id:3, SEL$3 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | PARALLEL(@"SEL$2" "e"@"SEL$2" 4) | | FULL(@"SEL$2" "e"@"SEL$2") | | GBY_PUSHDOWN(@"SEL$E1EFC3B3") | | PQ_GBY(@"SEL$E1EFC3B3" HASH) | | LEADING(@"SEL$E1EFC3B3" ("b"@"SEL$1" "tmp_books"@"SEL$3")) | | USE_HASH(@"SEL$E1EFC3B3" "tmp_books"@"SEL$3") | | PQ_DISTRIBUTE(@"SEL$E1EFC3B3" "tmp_books"@"SEL$3" BC2HOST NONE) | | PARALLEL(@"SEL$E1EFC3B3" "b"@"SEL$1" 4) | | FULL(@"SEL$E1EFC3B3" "b"@"SEL$1") | | UNNEST(@"SEL$3") | | SEMI_TO_INNER(@"SEL$42E9F535" "VIEW1") | | PRED_DEDUCE(@"SEL$3C119105") | | MERGE(@"SEL$3" > "SEL$548AE6DC") | | PARALLEL(4) | | OPTIMIZER_FEATURES_ENABLE('4.2.5.7') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | e: | | table_rows:1 | | physical_range_rows:3 | | logical_range_rows:3 | | output_rows:3 | | table_dop:4 | | dop_method:Global DOP | | avaiable_index_name:[epub_accountbook] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[DEFAULT] | | b: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | output_rows:1 | | table_dop:4 | | dop_method:Global DOP | | avaiable_index_name:[fi_voucher] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:1 | | estimation method:[DYNAMIC SAMPLING FULL] | | Plan Type: | | DISTRIBUTED | | Parameters: | | :0 => '1838983732300087318' | | :1 => '2091172744342274053' | | :2 => '2455462825193111554' | | Note: | | Degree of Parallelism is 4 because of hint | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 121 rows in set (0.00 sec) MySQL [test]> explain extended WITH tmp_books AS (SELECT /*+ materialize() parallel(4) */ id FROM epub_accountbook e WHERE e.id IN ('1838983732300087318','2091172744342274053','2455462825193111554')) SELECT max( b.accbook ) AS accbook_id FROM fi_voucher b WHERE b.accbook IN( SELECT id FROM tmp_books ); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =============================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------------------- | | |0 |TEMP TABLE TRANSFORMATION | |1 |7 | | | |1 |├─PX COORDINATOR | |0 |4 | | | |2 |│ └─EXCHANGE OUT DISTR |:EX10000 |0 |4 | | | |3 |│ └─TEMP TABLE INSERT |tmp_books|0 |4 | | | |4 |│ └─PX BLOCK ITERATOR | |3 |4 | | | |5 |│ └─TABLE GET |e |3 |4 | | | |6 |└─SCALAR GROUP BY | |1 |3 | | | |7 | └─PX COORDINATOR | |3 |3 | | | |8 | └─EXCHANGE OUT DISTR |:EX20001 |3 |3 | | | |9 | └─MERGE GROUP BY | |3 |2 | | | |10| └─SHARED HASH JOIN | |3 |2 | | | |11| ├─EXCHANGE IN DISTR | |1 |2 | | | |12| │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX20000 |1 |2 | | | |13| │ └─PX BLOCK ITERATOR | |1 |1 | | | |14| │ └─TABLE FULL SCAN |b |1 |1 | | | |15| └─TEMP TABLE ACCESS |tmp_books|3 |1 | | | =============================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_MAX(T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50))(0x7f781b9dc7b0)]), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | 2 - output(nil), filter(nil), rowset=16 | | dop=4 | | 3 - output(nil), filter(nil), rowset=16 | | 4 - output([e.id(0x7f781b838940)]), filter(nil), rowset=16 | | 5 - output([e.id(0x7f781b838940)]), filter(nil), rowset=16 | | access([e.id(0x7f781b838940)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([e.id(0x7f781b838940)]), range[1838983732300087318 ; 1838983732300087318], [2091172744342274053 ; 2091172744342274053], [2455462825193111554 | | ; 2455462825193111554], | | range_cond([e.id(0x7f781b838940) IN (cast('1838983732300087318'(0x7f8211a21ef0), VARCHAR(1048576))(0x7f8211a21610), cast('2091172744342274053'(0x7f8211a22dd0), | | VARCHAR(1048576))(0x7f8211a224f0), cast('2455462825193111554'(0x7f8211a23cb0), VARCHAR(1048576))(0x7f8211a233d0))(0x7f8211a20ea0)(0x7f8211a20560)]) | | 6 - output([T_FUN_MAX(T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50))(0x7f781b9dc7b0)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50))(0x7f781b9dc7b0)]) | | 7 - output([T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50)]), filter(nil), rowset=16 | | 8 - output([T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50)]), filter(nil), rowset=16 | | dop=4 | | 9 - output([T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(b.accbook(0x7f781b8688b0))(0x7f781b868e50)]) | | 10 - output([b.accbook(0x7f781b8688b0)]), filter(nil), rowset=16 | | equal_conds([b.accbook(0x7f781b8688b0) = tmp_books.id(0x7f781b868580)(0x7f781b86f410)]), other_conds(nil) | | 11 - output([b.accbook(0x7f781b8688b0)]), filter(nil), rowset=16 | | 12 - output([b.accbook(0x7f781b8688b0)]), filter(nil), rowset=16 | | dop=4 | | 13 - output([b.accbook(0x7f781b8688b0)]), filter(nil), rowset=16 | | 14 - output([b.accbook(0x7f781b8688b0)]), filter([b.accbook(0x7f781b8688b0) IN (cast('1838983732300087318'(0x7f781b838030), VARCHAR(1048576))(0x7f781b86a3e0), | | cast('2091172744342274053'(0x7f781b838300), VARCHAR(1048576))(0x7f781b86afc0), cast('2455462825193111554'(0x7f781b8385d0), VARCHAR(1048576))(0x7f781b86bba0))(0x7f781b879610)(0x7f781b8748b0)]), rowset=16 | | access([b.accbook(0x7f781b8688b0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([b.__pk_increment(0x7f781b86de20)]), range(MIN ; MAX)always true | | 15 - output([tmp_books.id(0x7f781b868580)]), filter(nil), rowset=16 | | access([tmp_books.id(0x7f781b868580)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | MATERIALIZE | | PARALLEL(4) | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$42E9F535 > SEL$3C119105 > SEL$548AE6DC > SEL$E1EFC3B3 | | stmt_id:2, SEL$2 | | stmt_id:3, SEL$3 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | PARALLEL(@"SEL$2" "e"@"SEL$2" 4) | | FULL(@"SEL$2" "e"@"SEL$2") | | GBY_PUSHDOWN(@"SEL$E1EFC3B3") | | PQ_GBY(@"SEL$E1EFC3B3" HASH) | | LEADING(@"SEL$E1EFC3B3" ("b"@"SEL$1" "tmp_books"@"SEL$3")) | | USE_HASH(@"SEL$E1EFC3B3" "tmp_books"@"SEL$3") | | PQ_DISTRIBUTE(@"SEL$E1EFC3B3" "tmp_books"@"SEL$3" BC2HOST NONE) | | PARALLEL(@"SEL$E1EFC3B3" "b"@"SEL$1" 4) | | FULL(@"SEL$E1EFC3B3" "b"@"SEL$1") | | UNNEST(@"SEL$3") | | SEMI_TO_INNER(@"SEL$42E9F535" "VIEW1") | | PRED_DEDUCE(@"SEL$3C119105") | | MERGE(@"SEL$3" > "SEL$548AE6DC") | | PARALLEL(4) | | OPTIMIZER_FEATURES_ENABLE('4.2.5.7') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | e: | | table_rows:1 | | physical_range_rows:3 | | logical_range_rows:3 | | output_rows:3 | | table_dop:4 | | dop_method:Global DOP | | avaiable_index_name:[epub_accountbook] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[DEFAULT] | | b: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | output_rows:1 | | table_dop:4 | | dop_method:Global DOP | | avaiable_index_name:[fi_voucher] | | stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] | | dynamic sampling level:1 | | estimation method:[DYNAMIC SAMPLING FULL] | | Plan Type: | | DISTRIBUTED | | Parameters: | | :0 => '1838983732300087318' | | :1 => '2091172744342274053' | | :2 => '2455462825193111554' | | Note: | | Degree of Parallelism is 4 because of hint | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 121 rows in set (0.01 sec)结论： 将 Hint
/*+ materialize parallel(4) */添加在 CTE 表达式内部的SELECT语句之前，该 Hint 生效。执行计划中Used Hint部分显示了MATERIALIZE和PARALLEL(4)，并且计划类型变为DISTRIBUTED，同时备注信息提示并行度为 4 是由 Hint 指定的。
备注
materialize物化 Hint 用于控制将视图或子查询物化而不展开，从而避免一条 SQL 语句的不同查询块（Query Block）中对同一个子查询进行多次重复计算。物化操作会在租户内存中写入临时文件，如果内存不足则会落盘。因此，如果需要物化的临时数据量较大并导致落盘，物化算子可能会比较慢。parallel并行 Hint 用于指定查询默认使用的并行度。需要注意的是，它是一个全局 Hint。因此，即使将该 Hint 添加在内层的子查询中，它对外层的 SQL 查询块也同样生效。
适用版本
OceanBase 数据库 V2.x、V3.x、V4.x 版本。
