oracle distinct 优化 no_merge,DM7 达梦数据库 查询优化 -- HINT 提示

在上节看了DM 执行计划的信息：

https://www.cndba.cn/dave/article/3633

这里看下hint的相关用法。

1 Hint 概述

DM 查询优化器采用基于代价的方法。在估计代价时，主要以统计信息或者普遍的数据分布为依据。在大多数情况下，估计的代价都是准确的。但在一些比较特殊的场合，例如缺少统计信息，或统计信息陈旧，或抽样数据不能很好地反映数据分布时，优化器选择的执行计划不是“最优”的，甚至可能是很差的执行计划。

开发人员和用户对于数据分布是很清楚的，他们往往能知道 SQL 语句按照哪种方法执行会最快。在这种情况下，用户可以提供一种方法，指示优化器按照固定的方法去选择 SQL 的执行计划。

DM 把这种人工干预优化器的方法称为 HINT，它使优化器根据用户的需要来生成指定的执行计划。如果优化器无法生成相应的执行计划，该 HINT 将会被忽略。

HINT 的常见格式如下所示：

SELECT /*+ HINT1 [HINT2]*/ 列名 FROM 表名 WHERE_CLAUSE ;

UPDATE 表名 /*+ HINT1 [HINT2]*/ SET 列名 =变量 WHERE_CLAUSE ;

DELETE FROM 表名 /*+ HINT1 [HINT2]*/ WHERE_CLAUSE ;

如果 HINT 的语法没有写对或指定的值不正确，DM 并不会报错，而是直接忽略 HINT 继续执行。

通过 V$HINT_INI_INFO 动态视图查询 DM 支持的 HINT。HINT 参数分为两类， HINT_TYPE 为“OPT”表示分析阶段使用的参数；HINT_TYPE 为“EXEC”表示运行阶段使用的参数，运行阶段使用的参数对于视图无效。

https://www.cndba.cn/dave/article/3633

SQL> select hint_type,count(1) from V$HINT_INI_INFO group by hint_type;

LINEID HINT_TYPE COUNT(1)

---------- --------- --------------------

1 OPT 140

2 EXEC 20

used time: 3.620(ms). Execute id is 18.

SQL>

2 索引提示

2.1 使用索引

目前 DM7 提供的 HINT 为表索引的选择 HINT，它指示使用指定索引进行数据检索。

语法：

表名 + INDEX + 索引名

或

/*+ INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)} */

一个语句中最多指定 8 个索引。在后一种语法格式中，如果查询中给出了表的别名那么必须使用别名。

假设表 t1 上 id 和 name 列上都存在着单列索引。

--数据准备

DROP TABLE T1 CASCADE;

CREATE TABLE T1 (ID INTEGER,NAME VARCHAR(128));

CREATE INDEX IDX_T1_ID ON T1(ID);

CREATE INDEX IDX_T1_NAME ON T1(NAME);

例 1 在查询语句中指定索引。

SELECT * FROM T1 WHERE ID > 2011 AND NAME < 'XXX';

如果 ID 列上能过滤更多数据，建议指示用索引 IDX_T1_ID。

SELECT * FROM T1 INDEX IDX_T1_ID WHERE ID > 2011 AND NAME < 'XXX';

或

SELECT /*+INDEX(T1, IDX_T1_ID) */ * FROM T1 WHERE ID > 2011 AND NAME < 'XXX';

例 2 当有多个索引时，要指定使执行计划最优的。

SELECT * FROM T1 WHERE ID > 2011 AND NAME < 'XXX' ORDER BY NAME;

考虑到后面的 NAME 列排序操作，建议指示使用 NAME 列的索引 IDX_T1_NAME，因为这样可以在执行过程中省略掉排序操作(执行计划中可以看出来)，比使用 ID 列索引代价小。

SELECT * FROM T1 INDEX IDX_T1_NAME WHERE ID > 2011 AND NAME < 'XXX' ORDER BY NAME;

或

SELECT /*+ INDEX(A IDX_T1_NAME)*/ * FROM T1 A WHERE ID > 2011 AND NAME < 'XXX' ORDER BY NAME;

2.2 不使用索引

语法：https://www.cndba.cn/dave/article/3633

/*+ NO_INDEX (表名[,] 索引名) { NO_INDEX (表名[,] 索引名)} */

可以指定多个索引，则这些索引都不能被使用。一个语句中最多指定 8 个索引。

3 连接方法提示

DBA 可以通过指定两个表间的连接方法来检测不同连接方式的查询效率，指定的连接可能由于无法实现或代价过高而被忽略。如果连接方法提示中的表名(别名)或索引名无效也会被自动忽略。

--数据准备

DROP TABLE T1 CASCADE;

DROP TABLE T2 CASCADE;

CREATE TABLE T1 (ID INTEGER,NAME VARCHAR(128));

CREATE TABLE T2 (ID INTEGER,NAME VARCHAR(128));

begin

for i in 1..1000 loop

insert into T1 val