Optimized Hint 类型下的 Access Path Hint

在 SQL 查询中，与访问路径相关的 Hint 允许开发者和数据库管理员指示查询优化器如何处理特定的表，它们影响查询时数据的读取方式。

Hint 类型	描述
`DYNAMIC_SAMPLING`	指定表级别动态采样。
`FULL`	指示优化器对指定表使用主键进行全表扫描。
`INDEX`	指定表使用特定索引。它的反向操作为 `NO_INDEX`。
`NO_INDEX`	指定表禁用特定索引。它的反向操作为 `INDEX`。
`INDEX_SS`	指定表使用特定索引并使用 skip scan。
`PARALLEL`	指定表级别并行度。
`USE_DAS`	优化器对指定表使用 DAS。它的反向操作为 `NO_USE_DAS`。
`NO_USE_DAS`	优化器对指定表禁用 DAS。它的反向操作为 `USE_DAS`。
`USE_COLUMN_TABLE`	指定对表对象使用列存扫描。它的反向操作为 `NO_USE_COLUMN_TABLE`。
`NO_USE_COLUMN_TABLE`	禁止对表对象使用列存扫描。它的反向操作为 `USE_COLUMN_TABLE`。
`INDEX_MERGE`	用于控制指定的表使用 index merge 计划。注意当指定的表后没有 `index_name` 参数的时候，表示控制指定的表使用 index merge 计划。当指定的表后有 `index_name` 参数的时候，表示控制指定的表使用指定的索引（Index）生成 index merge 计划。
`NO_INDEX_MERGE`	用于控制指定的表不使用指定的索引生成 index merge 计划。注意当指定的表后没有 `index_name` 参数的时候，表示不使用 index merge 计划。当指定的表后有 `index_name` 参数的时候，表示不使用指定的索引（Index）来生成 index merge 计划。

`DYNAMIC_SAMPLING` Hint

DYNAMIC_SAMPLING Hint 用于控制查询是否使用动态采样。开启动态采样可以提升查询执行计划的质量，尤其是在统计信息不充分或不精确时。

语法

/*+DYNAMIC_SAMPLING( [[ qb_name_option ] table_name ] INTNUM1 [, INTNUM2 ] ) */

参数解释

qb_name_option：可选，Query Block 的名称。
table_name：可选，控制动态采样的表名，如果为空则表示整个查询使用动态采样。
INTNUM1：指定采样的 Level（目前只支持 0 或者 1；1 表示开启动态采样，0 表示关闭动态采样）。
INTNUM2：指定采样的微块数（建议设置 32 个微块以上）。

示例

-- 为查询时对表 t1 采用动态采样功能
SELECT /*+DYNAMIC_SAMPLING(t1 1)*/ c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;

`FULL` Hint

FULL Hint 启用优化器对指定表使用主键进行扫描。

语法

/*+ FULL ( [ @queryblock ] tablespec ) */

示例

下方查询示例中，即使列 last_name 上存在索引，查询也会对 employees 表执行主键扫描。在 FROM 子句中使用别名 e 指定表 employees，因此 Hint 引用的是别名而不是表的实际名称。

首先，创建表 employees 并创建必要的数据和索引：

CREATE TABLE hr.employees (
    employee_id INT PRIMARY KEY,
    last_name VARCHAR(255),
    first_name VARCHAR(255)
);

-- 插入一些示例数据
INSERT INTO hr.employees (employee_id, last_name, first_name)
VALUES
(1, 'Smith', 'John'),
(2, 'Doe', 'Jane'),
(3, 'Brown', 'Charlie');

-- 创建索引
CREATE INDEX idx_last_name ON hr.employees(last_name);

接下来，使用 FULL Hint 强制对 employees 表进行主键扫描：

-- 强制对 employees 表使用主键进行扫描
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;

`INDEX` Hint

INDEX Hint 用于开启指定表使用特定索引。它的反向操作为 NO_INDEX Hint，表示指定表禁用特定索引。

语法

/*+ INDEX ( [ @queryblock ] tablespec indexspec ) */

行为

Hint 的行为取决于 indexspec 规范：

单个索引：一个 Hint 只能指定一个索引，优化器将仅考虑使用该索引进行扫描，而不考虑其他索引或全表扫描。
多个 Hint：可以为同一个表写入多个 INDEX Hint。例如，通过使用 /*+ INDEX(t idx1) INDEX(t idx2) */，优化器将评估这些指定的索引，并选择成本最低的索引进行扫描.在这种情况下，未被列出的索引和全表扫描同样不会被考虑。

一个单独的 INDEX Hint 只能指向一个索引。若要对同一表的多个索引提供提示，需分别为每个索引单独写入 INDEX Hint。

示例

-- 使用 INDEX Hint 指定查询优化器使用 'emp_department_ix' 索引访问 employees 表
SELECT /*+ INDEX (employees emp_department_ix) */ employee_id, department_id
FROM employees
WHERE department_id > 50;

`NO_INDEX` Hint

NO_INDEX Hint 用于禁用指定表使用特定索引。它的反向操作为 INDEX Hint，表示指定表开启特定索引。

语法

/*+ NO_INDEX ( [ @queryblock ] tablespec indexspec ) */

示例

-- 使用 NO_INDEX Hint 禁用查询优化器使用 'emp_department_ix' 索引访问 employees 表
SELECT /*+ NO_INDEX (employees emp_department_ix) */ employee_id, department_id
FROM employees
WHERE department_id > 50;

`INDEX_SS` Hint

INDEX_SS Hint 指定对表对象使用特定索引进行 index skip scan 扫描。

语法

/*+ INDEX_SS ( [ @ qb_name ] tablespec indexspec) */

参数解释

tablespec：为 hint 指定的表对象。
indexspec：为 index skip scan 扫描使用的索引。

示例

下方查询示例中，指定查询使用索引 idx1 进行 index skip scan 扫描，得到查询计划如下。查询中用于 index skip scan 的后缀 query range 为 (1,MIN ; 1,MAX)，此后缀 query range 从索引 idx1 的第二列 c2 开始抽取得到。

create table t1(c1 int, c2 int ,c3 int);
create index idx1 on t1(c1, c2);
create index idx2 on t1(c2);
explain basic select /*+index_ss(t1 idx1)*/ 1 from t1 where c2 = 1;
+-----------------------------------------------------------------------------------------------------+
| Query Plan                                                                                          |
+-----------------------------------------------------------------------------------------------------+
| =============================                                                                       |
| |ID|OPERATOR       |NAME    |                                                                       |
| -----------------------------                                                                       |
| |0 |TABLE SKIP SCAN|t1(idx1)|                                                                       |
| =============================                                                                       |
| Outputs & filters:                                                                                  |
| -------------------------------------                                                               |
|   0 - output([1]), filter([t1.c2 = 1]), rowset=16                                                   |
|       access([t1.c2]), partitions(p0)                                                               |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                   |
|       range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true |
|       prefix_columns_cnt = 1 , skip_scan_range(1,MIN ; 1,MAX)                                       |
+-----------------------------------------------------------------------------------------------------+

注意，即使使用了 INDEX_SS Hint，如果索引上无法抽取出用于 index skip scan 扫描的后缀 query range，查询最终仍不会使用 index skip scan，此时 INDEX_SS Hint 的作用仅仅是指定查询使用特定索引。

对于下面这两个例子，第一个查询使用 idx1 索引，直接抽取得到了 (1,MIN,MIN ; 1,MAX,MAX) 的 query range，无法进行 index skip scan 扫描；第二个查询使用 idx2 索引，抽取得到了 (1,MIN ; 1,MAX) 的 query range，也无法进行 index skip scan 扫描。此外，INDEX_SS Hint 没有对应的禁止使用 index skip scan 的反向 Hint，但是可以直接使用 INDEX Hint 指定索引，此时会直接禁止 index skip scan 扫描。

explain basic select /*+index_ss(t1 idx1)*/ 1 from t1 where c1 = 1;
+----------------------------------------------------------------------------------------+
| Query Plan                                                                             |
+----------------------------------------------------------------------------------------+
| ==============================                                                         |
| |ID|OPERATOR        |NAME    |                                                         |
| ------------------------------                                                         |
| |0 |TABLE RANGE SCAN|t1(idx1)|                                                         |
| ==============================                                                         |
| Outputs & filters:                                                                     |
| -------------------------------------                                                  |
|   0 - output([1]), filter(nil), rowset=16                                              |
|       access(nil), partitions(p0)                                                      |
|       is_index_back=false, is_global_index=false,                                      |
|       range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(1,MIN,MIN ; 1,MAX,MAX),  |
|       range_cond([t1.c1 = 1])                                                          |
+----------------------------------------------------------------------------------------+
explain basic select /*+index_ss(t1 idx2)*/ 1 from t1 where c2 = 1;
+-----------------------------------------------------------------------+
| Query Plan                                                            |
+-----------------------------------------------------------------------+
| ==============================                                        |
| |ID|OPERATOR        |NAME    |                                        |
| ------------------------------                                        |
| |0 |TABLE RANGE SCAN|t1(idx2)|                                        |
| ==============================                                        |
| Outputs & filters:                                                    |
| -------------------------------------                                 |
|   0 - output([1]), filter(nil), rowset=16                             |
|       access(nil), partitions(p0)                                     |
|       is_index_back=false, is_global_index=false,                     |
|       range_key([t1.c2], [t1.__pk_increment]), range(1,MIN ; 1,MAX),  |
|       range_cond([t1.c2 = 1])                                         |
+-----------------------------------------------------------------------+

`PARALLEL` Hint

PARALLEL Hint 是语句级的 Hint，用来指示优化器指定并行操作可使用的并行线程的数量。此 Hint 将覆盖初始化参数 PARALLEL_DEGREE_POLICY 的值。该 Hint 可以作为 SELECT、INSERT、UPDATE 和 DELETE 语句的一部分，以及表扫描的一部分。如果违反了任何的并行限制，则 PARALLEL Hint 被忽略。

语法

表级别的 PARALLEL Hint，语法如下：

/*+ PARALLEL(table_name n) */

同时使用 Hint 指定表级和全局并行度，表级 Hint 有更高优先级。

示例

-- 扫描表时，使用并行度为 8
SELECT /*+parallel(4) parallel(emp 8) */ last_name
FROM emp;

`USE_DAS` Hint

USE_DAS Hint 指示优化器对指定表使用 DAS（Distributed Data Access Service），这是 OceanBase AI 数据库中提供的分布式数据访问服务。通过启用 USE_DAS Hint，用户可以实现以下效果，从而充分利用 OceanBase AI 数据库的优势：

分区裁剪能力：通过维护数据与物理节点之间的映射关系，DAS 能够帮助优化器计算在 SQL 请求执行时所需的物理位置信息,提高了查询效率，减少不必要的数据扫描。
高效的基础数据访问：DAS 支持常见的数据库操作，如插入（insert）、更新（update）、删除（delete）、锁定行（lock_row）、以及扫描（scan）。保证在跨分布式环境中对数据的高效访问，使 SQL 请求能够在任意 Observer 节点上灵活访问集群中各个节点的数据。
任务级重试能力：在执行数据访问时，若因副本迁移或网络分区等原因导致某些分区不可读，DAS 会自动从其他可用副本重试请求。

语法

/*+ USE_DAS ( [ @queryblock ] tablespec ) */

示例

SELECT /*+ USE_DAS(t1) */ * FROM t1 WHERE column1 > 100;

`NO_USE_DAS` Hint

NO_USE_DAS Hint 指示优化器在处理指定表的查询时不使用 DAS。它的反向操作为 USE_DAS。

语法

/*+ NO_USE_DAS ( [ @queryblock ] tablespec ) */

示例

-- `NO_USE_DAS` Hint 通知优化器不要对表 t1 执行动态自适应采样，而是根据现有统计信息执行查询。
SELECT /*+ NO_USE_DAS(t1) */ * FROM t1 WHERE column1 < 50;

`USE_COLUMN_TABLE` Hint

USE_COLUMN_TABLE Hint 用于指定对表对象使用列存扫描。对于没有启用列存储的表，使用 USE_COLUMN_TABLE 无效。

语法

/*+ USE_COLUMN_TABLE ( [ @ qb_name ] tablespec) */

示例

create table t(c1 int, c2 int ,c3 int)
with column group (all columns, each column);

explain basic select /*+USE_COLUMN_TABLE(t)*/ * from t;
+------------------------------------------------------------------+
| Query Plan                                                       |
+------------------------------------------------------------------+
| ================================                                 |
| |ID|OPERATOR              |NAME|                                 |
| --------------------------------                                 |
| |0 |COLUMN TABLE FULL SCAN|t   |                                 |
| ================================                                 |
| Outputs & filters:                                               |
| -------------------------------------                            |
|   0 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=16     |
|       access([t.c1], [t.c2], [t.c3]), partitions(p0)             |
|       is_index_back=false, is_global_index=false,                |
|       range_key([t.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------+

`NO_USE_COLUMN_TABLE` Hint

NO_USE_COLUMN_TABLE Hint 指定禁止对表对象使用列存扫描。

语法

/*+ NO_USE_COLUMN_TABLE ( [ @ qb_name ] tablespec ) */

示例

create table t(c1 int, c2 int ,c3 int)
with column group (all columns, each column);

explain basic select /*+NO_USE_COLUMN_TABLE(t)*/ * from t;
+------------------------------------------------------------------+
| Query Plan                                                       |
+------------------------------------------------------------------+
| =========================                                        |
| |ID|OPERATOR       |NAME|                                        |
| -------------------------                                        |
| |0 |TABLE FULL SCAN|t   |                                        |
| =========================                                        |
| Outputs & filters:                                               |
| -------------------------------------                            |
|   0 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=16     |
|       access([t.c1], [t.c2], [t.c3]), partitions(p0)             |
|       is_index_back=false, is_global_index=false,                |
|       range_key([t.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------+

INDEX_MERGE Hint

INDEX_MERGE Hint 用于控制指定的表使用 index merge 计划。指定的索引不能生成 index merge 计划时忽略 Hint，没有指定索引则尽可能尝试 index merge 计划。

使用 INDEX_MERGE Hint 可以在 _enable_index_merge 配置项值为 False 时生成 index merge 计划。

语法如下：

/*+ INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */

参数解释：

@query_block_name：可选项，表示该 Hint 作用的查询块（query block）名称。如果省略，则默认对当前查询的主查询块生效。
tbl_name：表示要应用 index merge 计划的表名。
index_name：可选项，表示参与 index merge 计划的索引名称。可以指定多个索引名。
- 当指定的表后没有 index_name 参数的时候，表示控制指定的表使用 index merge 计划。
- 当指定的表后有 index_name 参数的时候，表示控制指定的表使用指定的索引（Index）生成 index merge 计划。

示例如下：

INDEX_MERGE Hint 中指定索引名称。

obclient> SELECT /*+ INDEX_MERGE(t1 idx_c2 idx_c3) */ * FROM t1
    WHERE c2 = 1
    AND c3 = 1
    AND c4 = 1;

INDEX_MERGE Hint 中不指定索引名称。

obclient> SELECT /*+ INDEX_MERGE(t1)*/ * FROM t1
    WHERE c2 = 1
    AND c3 = 1
    AND c4 = 1;

NO_INDEX_MERGE Hint

NO_INDEX_MERGE Hint 用于控制指定的表不使用指定的索引生成 index merge 计划。没有指定索引时禁止该表生成 index merge 计划。

当 NO_INDEX_MERGE Hint 与 INDEX_MERGE Hint 发生冲突时，忽略这两个 Hint。

语法如下：

/*+ NO_INDEX_MERGE([@query_block_name] tbl_name [index_name [,index_name]...]) */

参数解释：

@query_block_name：可选项，表示该 Hint 作用的查询块（query block）名称。如果省略，则默认对当前查询的主查询块生效。
tbl_name：表示表名。
index_name：可选项，表示索引名称，可以指定多个索引名。
- 当指定的表后没有 index_name 参数的时候，表示不使用 index merge 计划。
- 当指定的表后有 index_name 参数的时候，表示不使用指定的索引（Index）来生成 index merge 计划。

示例如下：

obclient> SELECT /*+ NO_INDEX_MERGE(t1)*/ * FROM t1
    WHERE c2 = 1
    AND c3 = 1
    AND c4 = 1;

注意

注意

DYNAMIC_SAMPLING Hint

语法

参数解释

示例

FULL Hint

语法

示例

INDEX Hint

语法

行为

示例

NO_INDEX Hint

语法

示例

INDEX_SS Hint

语法

参数解释

示例

PARALLEL Hint

语法

示例

USE_DAS Hint

语法

示例

NO_USE_DAS Hint

语法

示例

USE_COLUMN_TABLE Hint

语法

示例

NO_USE_COLUMN_TABLE Hint

语法

示例

INDEX_MERGE Hint

NO_INDEX_MERGE Hint

`DYNAMIC_SAMPLING` Hint

`FULL` Hint

`INDEX` Hint

`NO_INDEX` Hint

`INDEX_SS` Hint

`PARALLEL` Hint

`USE_DAS` Hint

`NO_USE_DAS` Hint

`USE_COLUMN_TABLE` Hint

`NO_USE_COLUMN_TABLE` Hint