OceanBase Optimizer Hint

HINT 机制可以使优化器生成某种特定的计划。

一般情况下，优化器会为用户查询选择最佳的执行计划，不需要用户使用 HINT 指定，但在某些场景下，优化器生成的执行计划可能不满足用户的要求，这时就需要用户使用 HINT 来指定生成某种执行计划。

HINT 语法

HINT 从语法上看是一种特殊的 SQL 注释，所不同的是在注释的左标记后（'/*' 符号）增加了一个“+”。既然是注释，如果服务器端无法识别 SQL 语句中的 HINT，优化器会选择忽略用户 HINT 而使用默认的计划生成逻辑结构。另外，HINT 只影响优化器生成计划的逻辑结构，而不影响 SQL 语句的语义。

{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hin_text]... */
*<span data-type="background" style="background-color: rgb(191, 191, 191);"></span>*

注意
如果使用 MySQL 的 C 客户端执行带 HINT 的 SQL 语句，需要使用 -c 选项登陆，否则 MySQL 客户端会将 HINT 作为注释从用户 SQL 中去除，导致系统无法收到用户 HINT。

HINT 参数

HINT 相关参数名称、语义和语法如下表：

名称	语法	语义
NO_REWRITE	NO_REWRITE	禁止 SQL 改写。
READ_CONSISTENCY	READ_CONSISTENCY (WEAK[STRONGFROZEN])	读一致性设置（弱/强）。
INDEX_HINT	/+ INDEX(table_name index_name) /	设置表索引。
QUERY_TIMEOUT	QUERY_TIMEOUT(INTNUM)	设置超时时间。
LOG_LEVEL	LOG_LEVEL([']log_level['])	设置日志级别，当设置模块级别语句时候，以第一个单引号(')作为开始，第二个单引号(')作为结束；例如‘DEBUG’。
LEADING	LEADING([qb_name] TBL_NAME_LIST)	设置联接顺序。
ORDERED	ORDERED	设置按照 SQL 中的顺序进行联接。
FULL	FULL([qb_name] TBL_NAME)	设置表访问路径为主表等价于 INDEX(TBL_NAME PRIMARY)。
USE_PLAN_CACHE	USE_PLAN_CACHE(NONE[DEFAULT])	设置是否使用计划缓存： NONE：表示不使用计划缓存 DEFAULT：表示按照其他变量进行设置
ACTIVATE_BURIED_POINT	ACTIVATE_BURIED_POINT(INTNUM, [FIX_MOD \| BEFORE_MODE], INTNUM, [INTNUM \| -INTNUM])	调试用，触发内部设定的错误点。
USE_MERGE	USE_MERGE([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用 MERGE JOIN。
USE_HASH	USE_HASH([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用 HASH JOIN。
NO_USE_HASH	NO_USE_HASH([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候不使用 HASH JOIN。
USE_NL	USE_NL([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用 NESTED LOOP JOIN。
USE_BNL	USE_BNL([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用 NESTED LOOP BLOCK JOIN
USE_HASH_AGGREGATION	USE_HASH_AGGREGATION([qb_name])	设置 aggregate 方法为使用 HASH AGGREGATE。例如 HASH GROUP BY 或者 HASH DISTINCT。
NO_USE_HASH_AGGREGATION	NO_USE_HASH_AGGREGATION([qb_name])	设置 aggregate 方法不使用 HASH AGGREGATE，使用 MERGE GROUP BY 或者MERGE DISTINCT。
USE_LATE_MATERIALIZATION	USE_LATE_MATERIALIZATION	设置使用晚期物化。
NO_USE_LATE_MATERIALIZATION	NO_USE_LATE_MATERIALIZATION	设置不使用晚期物化。
TRACE_LOG	TRACE_LOG	设置收集 trace 记录用于 SHOW TRACE 展示。
QB_NAME	QB_NAME( NAME )	设置 query block 的名称。
PARALLEL	PARALLEL(INTNUM)	设置分布式执行并行度。
TOPK	TOPK(PRECISION MINIMUM_ROWS)	设置模糊查询的精度和最小行数。其中 PRECSION 为整型，取值范围[0， 100]，表示模糊查询的行数百分比；MINIMUM_ROWS 为最小返回行数。

说明
qb_name 语法是: @NAME
TBL_NAME 语法是: [db_name.]relation_name [qb_name]

QB_NAME 介绍

在 DML 语句中，每一个 query_block 都会有一个 QB_NAME(query block name)，可以用户指定，也可以系统自动生成。在用户没有用 HINT 指定的 QB_NAME 的时候，系统会按照 SEL$1、SEL$2，UPD$1，DEL$1 方式从左到右（实际也是 Resolver 的解析顺序）依次生成。

通过 QB_NAME 可以精确定位每一个 table，也可以在一处地方指定任意 query block 的行为。在 TBL_NAME 中的 QB_NAME 用于定位 table，在 HINT 中最前面的 qb_name 用于定位 HINT 作用于哪一个 query_block。

如下例所示，按照默认规则，会为 SEL$1 中的 t 选择 t_c1 路径，为 SEL$2 中的 t 选择 PRIMARY（主表）访问。如果 SQL 通过 HINT 来指定 SEL$1 的 t 走主表，则 SEL$2 的 t 走索引。

obclient>CREATE TABLE t(c1 INT, c2 INT, KEY t_c1(c1));
Query OK, 0 rows affected (0.31 sec)

obclient>EXPLAIN SELECT * FROM t , (SELECT * FROM t WHERE c2 = 1) ta 
        WHERE t.c1 = 1\G；
*************************** 1. row ***************************
Query Plan: 
============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |1895|
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | TABLE SCAN                     |t      |1        |1397|
============================================================
Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t.c1], [t.c2]), filter(nil),
      access([t.c1], [t.c2]), partitions(p0)
  2 - output([t.c2], [t.c1]), filter([t.c2 = 1]),
      access([t.c2], [t.c1]), partitions(p0)

注意
因为改写后，SEL$2 被提升到 SEL$1 所以这里不用指定 HINT 作用的 query block。

obclient>EXPLAIN SELECT/*+INDEX(t@SEL$1 PRIMARY) INDEX(t@SEL$2 t_c1)*/ * 
        FROM t , (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G；
*************************** 1. row ***************************
Query Plan: 
=============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST |
-------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |16166|
|1 | TABLE SCAN                     |t      |1        |1397 |
|2 | TABLE SCAN                     |t(t_c1)|1        |14743|
=============================================================

Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t.c1], [t.c2]), filter([t.c1 = 1]),
      access([t.c1], [t.c2]), partitions(p0)
  2 - output([t.c2], [t.c1]), filter([t.c2 = 1]),
      access([t.c2], [t.c1]), partitions(p0)

此例中 SQL 也可以写成如下方式：

obclient>SELECT/*+INDEX(t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * FROM t ,
     (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G；
<==>
obclient>SELECT/*+INDEX(t@SEL$1 PRIMARY)*/ * from t , (SELECT/*+INDEX(t@SEL$2 t_c1)*/ * from t 
    WHERE c2 = 1) ta WHERE t.c1 = 1\G；
<==>
obclient>SELECT/*+INDEX(@SEL$1 t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * from t , 
     (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G；

对于 HINT 可以通过 EXPLAIN EXTENDED 查看 Outline Data 来学习。

obclient>EXPLAIN EXTENDED SELECT * 
FROM t , (SELECT * 
FROM t WHERE c2 = 1) ta 
                 WHERE t.c1 = 1\G;
*************************** 1. row ***************************
Query Plan: 
============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |1895|
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | TABLE SCAN                     |t      |1        |1397|
============================================================
Used Hint:
-------------------------------------
  /*+
 */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "test.t"@"SEL$2")
      LEADING(@"SEL$1" "test.t"@"SEL$1" "test.t"@"SEL$2")
      INDEX(@"SEL$1" "test.t"@"SEL$1" "t_c1")
      FULL(@"SEL$2" "test.t"@"SEL$2")
      END_OUTLINE_DATA
  */

HINT 一般规则

对于没有指定 query block 的 HINT 代表作用在本 query block。如下例所示，由于 t1 在 query block 2，同时无法改写提升到 query block 1，所以 HINT 无法生效。

obclient>CREATE TABLE t1(c1 INT, c2 INT, INDEX t1_c1(c1), INDEX
 t1_c2(c2));
Query OK, 0 rows affected (0.31 sec)

obclient>EXPLAIN SELECT/*+INDEX(t1 t1_c2)*/ * FROM t, 
       (SELECT * FROM t1 GROUP BY c1) ta WHERE t.c1 = 1\G;
*************************** 1. row ***************************
Query Plan: 
============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |666      |5906|
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | SUBPLAN SCAN                   |ta     |666      |5120|
|3 |  HASH GROUP BY                 |       |666      |4454|
|4 |   TABLE SCAN                   |t1     |1000     |1397|
============================================================
Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [ta.c1], [ta.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t.c1], [t.c2]), filter(nil),
      access([t.c1], [t.c2]), partitions(p0)
  2 - 
output([ta.c1], [ta.c2]), filter(nil),
      access([ta.c1], [ta.c2])
  4 - output([t1.c1], [t1.c2]), filter(nil),
      group([t1.c1]), agg_func(nil)
  5 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0)

如下例所示，SQL 可以发生改写，t1 提升到 SEL$1，则 HINT 生效。

obclient>EXPLAIN SELECT/*+INDEX(t1 t1_c2)*/ * FROM t, 
       (SELECT * FROM t1) ta WHERE t.c1 = 1\G;
*************************** 1. row ***************************
Query Plan: 
===============================================================
|ID|OPERATOR                        |NAME     |EST. ROWS|COST |
---------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|         |1000     |15674|
|1 | TABLE SCAN                     |t(t_c1)  |1        |472  |
|2 | TABLE SCAN                     |t1(t1_c2)|1000     |14743|
===============================================================
Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t1.c1], [t1.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0)
  2 - 
output([t.c1], [t.c2]), filter(nil),
      access([t.c1], [t.c2]), partitions(p0)

如果指定 table 行为，但在本 query block 中没有找到该 table，或者发生冲突，那么 HINT 无效。

对于没有找到 table 的 case 可以参考规则 1 中的第一个示例。以下示例为同时找到两个冲突的情况：

obclient>EXPLAIN EXTENDED SELECT/*+INDEX(t PRIMARY)*/ * 
                  FROM t , (SELECT * FROM t WHERE c1 = 1) ta 
                 WHERE t.c1 = 1\G;
*************************** 1. row ***************************
Query Plan: 
============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |970 |
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | TABLE SCAN                     |t(t_c1)|1        |472 |
============================================================
Outputs & filters:
-------------------------------------
  0 - output([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)], [t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), filter(nil),
      conds(nil), nl_params_(nil), inner_get=false, self_join=false, batch_join=false
  1 - output([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)]), filter(nil),
      access([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)]), partitions(p0),
      is_index_back=true,
      range_key([t.c1(0x7f7b7cdd3e60)], [t.__pk_increment(0x7f7b7cde86e0)]), range(1,MIN ; 1,MAX),
      range_cond([t.c1(0x7f7b7cdd3e60) = 1(0x7f7b7cdd3800)])
  2 - 
output([t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), filter(nil),
      access([t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), partitions(p0),
      is_index_back=true,
      range_key([t.c1(0x7f7b7cdd2bd0)], [t.__pk_increment(0x7f7b7cdf41b0)]), range(1,MIN ; 1,MAX),
      range_cond([t.c1(0x7f7b7cdd2bd0) = 1(0x7f7b7cdd2570)])

Used Hint:
-------------------------------------
  /*+
  */

联接方法的 HINT 中指定的 table 如果找不到，忽略该 table，其他的指定依然生效；如果优化器不能生成指定的联接方法，就会选择其他方法，HINT 无效。
联接顺序的 HINT 中如果存在 table 无法找到，则该 HINT 完全失效。

HINT 主要语法

与其他数据库的行为相比，OceanBase 数据库优化器是动态规划的，已经考虑了所有可能的最优路径，HINT 主要作用是指定优化器的行为，并按照 HINT 执行。

INDEX HINT

INDEX HINT 的语法同时支持 MySQL 和 Oracle 方式。

INDEX HINT 的 Oracle 语法如下：

obclient> SELECT/*+INDEX(table_name index_name) */ * FROM table_name;

INDEX HINT 的 MySQL 语法如下：

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
index_hint [, index_hint] ...

index_hint:
USE {INDEX|KEY}
  [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
index_name [, index_name] ...

Oracle 语法中一个表只能指定一个 INDEX，MySQL 语法可以指定多个。但是 OceanBase 数据库中 MySQL 语法虽然支持指定多个，但是对于 USE 和 FORCE，只会用第一个 INDEX 生成 PATH，即使 SQL 语句中没有该 INDEX 的 filter 而导致全部扫描同时回表（即 OceanBase 数据库当前设计是认为写 HINT 的人比程序更明白那条路径是更好的）。IGNORE 类型会忽略所有指定的 INDEX。USE、 FORCE 和 Oracle HINT 方式实际是一样的，该方式的 INDEX 不存在或者处于 invalid 状态，则 HINT 无效。对于 IGNORE 方式，如果将包括主表 (primary) 在内的所有 INDEX 忽略，则 HINT 无效。

FULL HINT

FULL HINT 的语法是用于指定表使用主表扫描，语法如下：

/*+ FULL(table_name)*/

FULL HINT 用于指定表选择主表扫描等价于 INDEX HINT /*+ INDEX(table_name PRIMARY)*/。

ORDERED HINT

ORDERED HINT 可以指定按照 from 后面的表的顺序作为联接顺序，语法如下：

/*+ ORDERED*/

如果指定该 HINT 后发生改写，那么就按照改写后的 stmt 中 from items 的顺序联接，因为改写时候 sub_query 会在 from items 中对应位置填放新的 table item。

LEADING HINT

LEADING HINT 可以指定表的联接顺序，语法如下：

/*+ LEADING(table_name_list)*/

table_name_list 中 table_name 比较特殊，其他 table_name 语法如下：

db_name . relation_name

relation_name

.relation_name

在 table_name_list 中 table_name 语法如下：

db_name . relation_name

relation_name

table_name_list 语法如下：

table_name
table_name_list table_name
table_name_list, table_name

LEADING HINT 为确保按照用户指定的顺序联接检查比较严格，如果发现 HINT 指定的 table_name 不存在，LEADING HINT 失效；如果发现 HINT 中存在重复 table，LEADING HINT 失效。如果在 optimizer 联接期间，按 table_id 无法在 from items 中找到对应的，即可能发生改写，那么该 table 及后面的 table 指定的 JOIN 序失效，前面的依然有效。

Use_merge

可以指定表在 JOIN 时候使用 merge-join 算法，语法为：/*+ USE_MERGE(table_name_list) */

使用 merge-join 将 use_merge 指定的表作为右表。

注意
OceanBase 数据库中 merge-join 必须有等值条件的 join-condition，因此无等值条件的两个表联接，use_merge 会无效。

关于 merge-join 是否认为 A merge-join B 等效于 B merge-join A 当前并没有最后结论。按照代价模型，merge-join 计算代价时是区分左右表的。同时考虑到区分左右表可以增加 HINT 灵活性，当前 merge-join 区分左右表，即 use_merge 仅对表作为右表的时候生效。

Use_nl

指定表作为右表在联接的时候使用 NESTED LOOP JOIN 算法，语法如下：

/*+ USE_NL(table_name_list) */

Use_hash

指定表作为右表在联接的时候使用 HASH JOIN 算法，语法如下：

/*+ USE_HASH(table_name_list) */

Parallel

指定语句级别的并发度。当该 HINT 指定时，会忽略系统变量 ob_stmt_parallel_degree 的设置。语法如下：

/*+ PARALLEL(4) */

w3cschool 编程狮，随时随地学编程

OceanBase Optimizer Hint

HINT 语法

HINT 参数

QB_NAME 介绍

HINT 一般规则

HINT 主要语法

INDEX HINT

FULL HINT

ORDERED HINT

LEADING HINT

Use_merge

Use_nl

Use_hash

Parallel

OceanBase 产品简介

OceanBase 快速入门

OceanBase 教程

OceanBase 控制台指南

OceanBase 集群工作台

OceanBase 租户

OceanBase 监控

OceanBase 诊断

OceanBase 备份恢复管理

OceanBase 安全设置

OceanBase 参数管理

OceanBase 数据传输

OceanBase 产品简介

OceanBase 产品架构

OceanBase 产品功能

OceanBase 快速入门

OceanBase 迁移项目管理

OceanBase MySQL数据库迁移

OceanBase Oracle数据库迁移

OceanBase 常见问题

OceanBase ODC 使用指南

OceanBase Web 版 ODC

OceanBase 连接数据库

OceanBase 使用工作台

OceanBase 使用工具

OceanBase 数据导出和导入

OceanBase 任务管理

OceanBase 数据库对象

OceanBase 表对象

OceanBase 视图对象

OceanBase 函数对象

OceanBase 存储过程对象

OceanBase 序列对象

OceanBase 程序包对象

OceanBase 触发器对象

OceanBase 类型对象

OceanBase 同义词对象

OceanBase 客户端版 ODC

OceanBase 连接数据库

OceanBase 使用工作台

OceanBase 使用工具

OceanBase 数据导出和导入

OceanBase 任务管理

OceanBase 数据库对象

OceanBase 表对象

OceanBase 视图对象

OceanBase 函数对象

OceanBase 存储过程对象

OceanBase 序列对象

OceanBase 程序包对象

OceanBase 触发器对象

OceanBase 类型对象

OceanBase 同义词对象

OceanBase Connector/J 开发者指南

OceanBase 什么是OceanBase Connector/J

OceanBase Connector/J 使用指南

OceanBase 基本操作

OceanBase Java数据流

OceanBase 使用LOB

OceanBase 数据源和URL

OceanBase 结果集

OceanBase 语句缓存

OceanBase 参考信息

OceanBase Oracle 模式特有功能

OceanBase 分布式事务