--- title: 如何为 CTE 公共表达式正确地添加 Hint-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于如何为 CTE 公共表达式正确地添加 Hint相关的常见问题和使用技巧，帮助您快速解决如何为 CTE 公共表达式正确地添加 Hint的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 如何为 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。 ```sql -- 递归的 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) ``` ```sql -- 非递归的 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。 ```sql -- 递归的 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 1 ``` ```sql obclient(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 将无效。 ## 测试验证 1. 准备测试表结构。 ```sql 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 ); ``` 2. 验证在不同位置添加 Hint 是否生效。 - 将 Hint 直接添加在 `WITH` 关键字后面（无效）： ```sql 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 表达式的括号内（生效）： ```sql 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 版本。 Previous [OceanBase 数据库内 SQL 执行出现 size overflow，日志出现 thread count reach limit 的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002397695) Next [OceanBase 数据库 GTS 服务使用指南](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002396303) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线