SinoDB 动态服务器优化器是一个非常复杂的工具，一般而言，它能够选取最高效的查询计划，不过我们也可以通过配置优化器选项来影响优化器的选择。
1. 优化器的影响因素
-
OPTCOMPIND
— 配置参数
— 环境变量
— 会话级 SQL 语句 -
SET OPTIMIZATION
— High/Low
— All Rows/First Rows -
OPTIMIZER DIRECTIVES
1.1 OPTCOMPIND
| OPTCOMPIND | 优化器偏好 |
|---|---|
| 0 | 访问路径：当索引可用时只进行索引扫描。连接方案：倾向于嵌套循环连接（nested-loop joins） |
| 1 | 如果隔离级别是repeatable read，那么与OPTCOMPIND=0时相同。否则，与OPTCOMPIND=2时相同 |
| 2 | 默认值，常规优化，选择成本最低的查询计划 |
可以通过onconfig配置文件中的OPTCOMPIND 配置参数来控制优化器的行为进而影响在数据库服务器上执行的所有查询操作。也允许用户使用SET ENVIRONMENT OPTCOMPIND SQL 语句在会话级指定OPTCOMPIND。
例如：
SET ENVIRONMENT OPTCOMPIND ‘2’;
使用 SET ENVIRONMENT OPTCOMPIND 命令设置的值的优先级高于配置文件中设置的值。OPTCOMPIND 环境变量的缺省设置将在当前会话结束时恢复。会话执行 SET ENVIRONMENT OPTCOMPIND 语句不会影响其他用户会话。
运行 SET ENVIRONMENT OPTCOMPIND DEFAULT，将会话恢复至配置文件中指定的配置。
1.2 SET OPTIMIZATION
SET OPTIMIZATION SQL 语句允许指定优化目标以及允许优化器考虑多条查询路径所需的时间。
-
缺省值为 HIGH 和 ALL_ROWS
-
作用于整个处理过程或直到下一条 SET OPTIMIZATION 语句执行
-
每条语句设置一个选项，如有必要可使用两条语句
1.2.1 FIRST_ROWS vs. ALL_ROWS
优化查询的目标可能是快速检索行的第一个缓冲区，或者以最快的方式检索所有行。如果应用程序是一种终端用户查询工具，我们可以选择FIRST_ROWS 。
也许我们的终端用户是需要执行一系列假设-分析（ what-if ） 方案的金融分析师。为运行每个方案，他提交一个需要检索大量行的查询。查看几行之后，他意识到该方案不会生成他想要的结果，因而转向下一个查询。通过选择 FIRST_ROWS 优化，终端用户可以更快的从数据库服务器获取到反馈结果，使得他工作效率更高。
但是，对于一个为几千个员工处理薪资更新的批处理应用程序来说，ALL_ROWS 优化可能是最合适的优化方式，将有可能获得最佳性能。
1.2.2 HIGH vs. LOW
HIGH 和 LOW 选项影响优化器用于分析查询路径的时间。HIGH 选项指示优化器使用复杂的算法检查所有合理的查询方案，并从中选择最佳方案。
LOW 选项使用不太复杂但更快的算法。该算法在优化阶段的早期就消除了更多的连接选项，减少了用于分析可行方案的时间。
OPTIMIZATION LOW
在上述的 SET OPTIMIZATION LOW 示例中，展示了优化器可能的选择查询路径的方式。 选择 ac 作为成本最低的两路连接，不再检查其他两路连接。下一步，检查可能来自 ac 连接的三路连接。同样，只有成本最低的连接会持续到下一级。正如所看到的，必须检查的连接数在显著减少。
SET OPTIMIZATION LOW 语句减少了优化查询的时间，但是增加了选择非最高效查询方案的风险。
1.2.3 何时尝试SET OPTIMIZATION LOW
-
仅当查询时间不可接受
-
对于连接五张或更多表的查询
-
所有连接的列均被索引时，这种情况下优化器即使在使用 LOW 算法时选择恰当路径的机率也会更大。
-
查询中的一张表与查询中的其他表均有连接
SET OPTIMIZATION LOW 语句一直有效，直到会话与数据库服务器断开连接，或直到执行 SET OPTIMIZATION HIGH 命令。
1.2.4 FIRST_ROWS
SET OPTIMIZATION FIRST_ROWS 语句有助于决策支持环境（decision support environment）、联机查询（on-line query）和报表操作（report activities）。它指示优化器选择返回行的第一个缓冲区最快的查询路径，即使增加了检索所有行的时间。
使用 SQL 语句进行设置：
SET OPTIMIZATION FIRST_ROWS;
优化目标保持有效，直到进程结束，或者直到执行一条语句来设置 ALL_ROWS 优化。也可以通过在 ONCONFIG配置文件中设置 OPT_GOAL 参数，为您的实例指定 FIRST ROWS 优化为缺省设置。例如：
# Optimization goal:-1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1
另外，可以使用环境变量 OPT_GOAL 的为特定用户的环境设置优化目标。
# ksh
export OPT_GOAL=0
如果只想对单个查询使用FIRST_ROWS优化，则可以使用 FIRST_ROWS 优化器指令。
SELECT --+ FIRST_ROWS
fname, lname FROM customer
ORDER BY sname;
2. 优化指示器
2.1 使用指示
-
积极指示—正向指示
积极指示指示优化器将其选择范围限制在特定路径集合内。 -
消极指示—负向指示
消极指示指示优化器规避确定的非最佳的路径。优化器依然会考虑所有其他路径。如果一条新的索引提供一条改进的路径，不需改变指示，优化器自动选择新的路径。
如果外圈是所有可能的查询路径集合，积极指示指示优化器将考虑的路径范围限制在蓝色内圈中。消极指示允许优化器选择定义在外圈内的但不在内圈中的任一路径。
尽量使用消极指示而非积极指示。这保证了指示需要更少的维护，因为当索引增加和移除或者数据分布改变时，优化器可自由的选择一个存在且更优的路径。
2.2 优化器指示的类型
指令支持控制以下范畴的的优化流程：
-
访问方式（access-method）指示——索引 vs. 扫描
-
连接顺序（join-order）指示——指定连接表的顺序
-
连接方式（join-method）指示——强制哈希连接或嵌套循环连接
-
优化目标（optimization-goal）指示——更快的响应时间 vs. 吞吐量
-
解释（explain）指示——生成查询方案的输出
优化器指示允许查询开发人员在 SQL 语句创建查询计划期间影响优化器。优化器指令提供了指示优化器遵循特定路径而不是通过其分析选择计划的灵活性。这可以减少纠正性能问题所需的时间。重写查询可能非常费时，上述提供了一种可以快速修改方案并进行测试的方法。
2.3 识别指示
通过在注释符号跟着加号来识别指示：
SELECT –+ORDERED,INDEX(c cust_idx)
*
FROM customer c, orders o
WHERE c.customer_num BETWEEN 101 AND 1001
AND c.customer_num = o.customer_num;
SqlExplain 输出如下：
…
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:
INDEX ( c cust_idx ) Invalid Index Name Specified.
Estimated cost: 4
…
有效的注释语法包括：
–+directive text
{+directive text}
/*+directive text */
指示也可指示优化器应该避开什么而不仅仅是选择什么。这允许编写一条指示来规避已知的会对查询造成性能影响的特定操作。当增加新的索引或表属性时，优化器仍可对其进行检查。
如果遵循指令，在SET EXPLAIN输出中它将列在“DIRECTIVES FOLLOWED”下。如果未遵循指令，则该指令将列在“DIRECTIVES NOT FOLLOWED”下并会列出不遵循指令的原因。在以上示例的SqlExplain输出中，提示为客户表指定的索引不存在。
2.3.1 访问方式指示
SinoDB动态服务器提供了以下几种访问方式指示：
- INDEX——强制使用所有索引（或某一指定索引）
如果指定一个索引，该索引将会被使用。如果指定多个索引，将会基于成本的列表选取索引。如果不声明特定索引，那么所有索引都会被考量。除非表没有索引，否则不考虑顺序扫描。例如：
SELECT –+ INDEX (e salary_indx)
name, salary
FROM emp e WHERE e.dno = 1 AND e.salary > 50000;
-
AVOID_INDEX——不使用索引（或某一指定索引）。
允许优化器考量写入查询后所增加的索引，规避会导致查询变慢的已知索引。 -
FULL——强制优化器在指定的表上执行顺序扫描，即使表的列上存在索引。例如：
SELECT –+FULL(e)
name, salary
FROM emp e；
- AVOID_FULL——规避全表顺序扫描（如果可以将使用索引）。
该指示可与 REPEATABLE READ 隔离级别同时使用，例如，规避全表扫描和顺序锁定。
SELECT –+AVOID_FULL(e),INDEX (e salary_indx)
name, salary
FROM emp e
WHERE e.department_num = 1 AND e.salary > 5000.;
只要指示处于同一个注释区域内，它们就可以被同时使用。
-
INDEX_SJ —— 在指定索引上强制使用自联接
在指定索引上强制使用自联接路径，或选择索引列表中成本最低的索引，即使该索引前导键的数据分布统计信息不可用。 -
AVOID_INDEX_SJ —— 不对指定的索引执行自联接
通常，每个表只能指定一个访问方法指令。只有以下访问方式指令组合对同一查询中的同一表有效：
- AVOID_FULL, AVOID_INDEX
- AVOID_FULL, AVOID_INDEX_SJ
- AVOID_INDEX, AVOID_INDEX_SJ
- AVOID_FULL, AVOID_INDEX, AVOID_INDEX_SJ
- INDEX, AVOID_INDEX_SJ
如果使用 INDEX、AVOID_INDEX_SJ指令，则指令AVOID_INDEX_SJ索引必须是 INDEX 指令中索引的子集
2.3.2 连接顺序指示
基于FROM子句中表的顺序使用 ORDERED 指示访问表
SELECT –+ORDERED
c.customer_num, sum(total_price)
FROM customer c, orders o, items i
WHERE c.customer_num = o.customer_num AND o.order_num = i.order_num
GROUP BY 1;
ORDERED 指示尤其有益于通过基于规则的优化器（rules-based optimizers）编写的应用程序。因为基于规则的优化器一般会根据表在 FROM 子句中的顺序来连接表，熟悉基于规则的优化器的开发者可以由此构建 SQL 语句.
2.3.3 连接方式指示
连接方式指示允许控制优化器是否执行嵌套循环连接或哈希连接（hash join）。
- USE_NL
- AVOID_NL
- USE_HASH
- /BUILD
- /PROBE
- AVOID_HASH
连接方式指示允许决定嵌套循环连接中哪些表是内表，使用哈希连接时哪些表是被哈希(build table)或被探测(probe table)。
- USE_NL（tablename）
USE_NL 指示可以用于强制嵌套循环连接。在嵌套循环连接中，外表中的每行都会被用于探测内表以发现匹配的行。连接的行会作为结果返回。访问内部表可以通过扫描、现有索引或动态构建索引的方式。
USE_NL 将表名作为参数。可以指定的表的最大数量应比表的总数少1，因为必须使用一张表作为外部表用于嵌套循环的顺序。
SELECT –+USE_NL (dept)
name, title, salary, dname FROM emp, dept, job
WHERE loc = “Palo Alto”
AND emp.department_num = dept.department_num
AND emp.job = job.job;
这使得优化器使用嵌套循环连接来连接dept 表与查询中的其他表。dept 表是连接的内部表。
-
AVOID_NL（tablename ）
AVOID_NL 可被用于强制优化器在指定表上规避嵌套循环连接。 -
USE_HASH（tablename ）
使用 USE_HASH 指令强制哈希联接。在哈希连接中，其中一个表的行用于为每个元素构建哈希表。然后，通过探测处理第二个表，直接将探测的值哈希到哈希表中并检查联接对。
没有任何参数的 USE_HASH 指示优化器使用哈希连接连接表，并根据成本确定连接的顺序。可以在指示中指定哪张表是构建表(build table)或探测表(probe table)。
SELECT –+USE_HASH (dept/BUILD)
name, title, salary, dname FROM emp, dept, job
WHERE loc = “Palo Alto”
AND emp.department_num = dept.department_num
AND emp.job = job.job;
该指示使得优化器使用哈希连接连接dept 表，并在dept 表上构建哈希表。
- AVOID_HASH (tablename )
AVOID_HASH 强制优化器规避哈希连接。可以根据需要限定表为构建表(build table)或探测表(probe table)。
2.3.4 优化目标指示
- FIRST_ROWS
— 允许指示优化器选择一个尽可能快的检索出first screenful of rows的方案。
— 有助于交互报表：
SELECT –+FIRST_ROWS
FIRST 50 *
FROM customer;
- ALL ROWS
— 缺省值
— 优化器将选择最快路径用以在结果集合中检索所有行
FIRST_ROWS 指示对于开发者和终端用户而言极其有用。对于开发者而言，它是一款简单的工具，可以与 SQL FIRST 关键字一起使用来快速的从一张非常大的表中访问一个小样本数据集。
另外，如果开发者把 FIRST_ROWS 优化指示添加到需要终端用户屏幕输入的查询中，终端用户将得到更快的系统响应。更快的系统响应意味用户体验更好！
2.3.5 解释指示
SELECT --+ORDERED, INDEX(c idx_badname), EXPLAIN
c.customer_num, sum(total_price)
FROM customer c, orders o, items i
WHERE c.customer_num = o.customer_num AND o.order_num = i.order_num
GROUP BY 1;DIRECTIVES FOLLOWED:ORDERED
EXPLAIN
DIRECTIVES NOT FOLLOWED:
INDEX ( c idx_badname ) Invalid Index Name Specified.Estimated Cost:17
Estimated # of Rows Returned:1
- stu101.c:INDEX PATH …
EXPLAIN 指示允许直接在查询中打开 SQL 的解释功能。EXPLAIN 功能对于测试指示非常有用。
3. 指示的使用
可用于：
-
SELECT、UPDATE 和 DELETE 语句中
-
嵌套在 INSERT 语句内的 SELECT 语句中
-
视图、存储的过程和触发器中
不可用于：
-
访问远程表的分布式查询中
-
对于 UPDATE/DELETE WHERE CURRENT OF 语句
在同一个注释区块中可使用多条指示
4. 使用指示的建议
在查询中使用指示时，可应用以下指南：
-
频繁的检测指示的有效性。
表结构、可用的索引、数据分布及其他变更可能会改变查询的最佳路径。 -
尽可能使用消极指示。
尽可能使用消极指示而不是积极指示。积极指示将优化器限制为单一选择，例如总是在该表上执行索引扫描 ，但是消极指示只排除一个差的选择，给优化器留下来的是其它可用选择，限制更少。 -
通过设置，禁用所有指示：
DIRECTIVES 配置参数为 0
IFX_DIRECTIVES 环境变量为 OFF
5. 外部指示
- 允许通过指示在不可修改的应用程序中动态替换 SQL
- 使用 SAVE EXTERNAL DIRECTIVE SQL 语句
- 需要 ONCONFIG 参数和会话环境变
- 保存到 sysdirectives 系统目录表中
- 示例：
SAVE EXTERNAL DIRECTIVES /*+ avoid_full(table1) */ ACTIVE
FOR SELECT * FROM table1 WHERE col_1 > 5000;
SinoDB允许动态重写一条SQL 语句来增加指示。当无法重写查询以短期解决问题时（例如，当查询开始执行不佳时），外部优化器指令非常有用。
只有数据库DBA 可以保存外部指示。通过 SAVE EXTERNAL DIRECTIVES SQL 语句完成上述操作。该语句由指示、状态和应用的 SQL 语句组成。状态是 ACTIVE、INACTIVE 或 TEST_ONLY。
为了改变或删除外部指示的状态，数据库管理用户必须直接更新 sysdirectives 表。
为了让外部指示运行，必须在初始化时将 EXT_DIRECTIVES 配置参数设置为大于0，而且 IFX_EXTDIRECTIVES 变量可能需要在客户端上设置。