Oracle 19C 关于优化器提示

原创 Asher.HU 2021-02-04

733

优化程序提示是SQL语句中的特殊注释，这些指令将指令传递给优化程序。

除非受到某些条件的限制，否则优化器会使用提示为语句选择执行计划。

提示嵌入在SQL注释中。

提示注释必须紧跟在SQL语句块的第一个关键字之后。您可以使用任何一种注释风格：斜杠（/*）或破折号（--）。加号（+）提示定界符必须紧跟在注释定界符之后，且加号前不允许有空格，如以下片段所示：

SELECT /*+ hint_text */ ...

加号后的空格是可选的。语句块只能有一个包含提示的注释，但它可以包含许多以空格分隔的提示。用至少一个空格分隔多个提示，如以下语句所示：

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;

提示使您能够做出优化器通常做出的决策。

您可以使用提示来影响优化器模式，查询转换，访问路径，联接顺序和联接方法。在测试环境中，提示对于测试特定访问路径的性能很有用。例如，您可能知道索引对于某些查询更具选择性，从而导致更好的计划。下图显示了如何使用提示来告诉优化器为特定的语句使用特定的索引。

图19-2优化程序提示

提示的缺点是需要额外的代码来管理，检查和控制。当优化程序生成次优计划时，用户几乎没有追索权时，在Oracle7中引入了提示。因为数据库和主机环境中的更改可能使提示过时或产生负面影响，所以一个好的实践是使用提示进行测试，但使用其他技术来管理执行计划。

Oracle提供了多种工具，包括SQL Tuning Advisor，SQL计划管理和SQL Performance Analyzer，以解决优化程序无法解决的性能问题。Oracle强烈建议您使用这些工具，而不要使用提示，因为它们会随着数据和数据库环境的变化而提供新的解决方案。

父主题： 关于优化器提示

您可以对表，查询块和语句使用提示。

提示分为以下几种类型：

单表
在一个表或视图上指定单表提示。INDEX并且USE_NL是单表提示的示例。以下语句使用单表提示：
```
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM   employees 
WHERE  department_id > 50;
```
多表
多表提示类似于单表提示，不同之处在于该提示可以指定多个表或视图。LEADING是多表提示的示例。以下语句使用多表提示：
```
SELECT /*+ LEADING(e j) */ *
FROM   employees e, departments d, job_history j
WHERE  e.department_id = d.department_id
AND    e.hire_date = j.start_date;
```
注意：
USE_NL(table1 table2)不被认为是多表暗示，因为它是一个快捷方式USE_NL(table1)和USE_NL(table2)。
查询块
查询块提示在单个查询块上运行。STAR_TRANSFORMATION并且UNNEST是查询块提示的示例。以下语句使用查询块提示来指定该FULL提示仅适用于引用的查询块employees：
```
SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)
FROM   jobs t1
WHERE t1.job_id IN (SELECT job_id FROM employees t1);
```
声明
语句提示适用于整个SQL语句。ALL_ROWS是语句提示的示例。以下语句使用语句提示：
```
SELECT /*+ ALL_ROWS */ * FROM sales;
```

也可以看看：

《 Oracle数据库SQL语言参考》中按功能类别提供的最常见提示。

当您在语句块中指定提示时，该提示将应用于相应的查询块，表或语句块中的整个语句。该提示将覆盖所有实例级或会话级参数。

一个语句块是以下情况之一：

示例19-1使用集合运算符的查询

以下查询由两个组件查询和UNION运算符组成：

SELECT /*+ FIRST_ROWS(10) */ prod_id, time_id FROM 2010_sales
UNION ALL
SELECT /*+ ALL_ROWS */ prod_id, time_id FROM current_year_sales;

前面的语句有两个块，每个组件查询一个。第一个组件查询中的提示仅适用于其优化，而不适用于第二个组件查询的优化。例如，在2015年的第一周，您查询当年和去年的销售额。您将应用FIRST_ROWS(10)到去年（2014）的销售ALL_ROWS查询以及对今年（2015）的销售查询的提示。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者