学习ORACLE-优化器提示（Hints）

最新推荐文章于 2021-03-27 13:04:19 发布

储加富

最新推荐文章于 2021-03-27 13:04:19 发布

阅读量1.9k

点赞数

分类专栏：学习 oracle 文章标签： HINT Oracle

本文链接：https://blog.csdn.net/qq_33316544/article/details/89056687

版权

本文详细介绍了Oracle数据库中的优化器提示（Hints）的使用，包括不同类型和分类的提示，如指定优化方法、启用特性、访问路径和联接顺序等。通过示例展示了如何使用这些提示来影响查询执行计划，优化数据仓库中的数据处理效率。

摘要由CSDN通过智能技术生成

数据仓库中加工数据时，经常会使用提示（HINT）指示优化器改变执行路径，以便快速的得到数据。现总结一下提示的使用。内容主要来源于Oracle docs 和网络。

优化器提示

1.优化器提示概述

1.1 提示的类型

提示可以是以下几种一般的类型：
1.单表：提示指定在一个表或这视图上，例如INDEX 和USE_NL；
2.多表：与单表提示类似，只是提示能够指定在一个或多个表和视图上。例如LEADING是一个多表提示。注意USE_NL(table1 table2)不是多表提示，只是USE_NL(table1)和USE_NL(table2)的简写；
3.查询块：作用在单个查询块。例如STAR_TRANSFORMATION和UNNEST；
4.语句：语句提示应用到整个SQL语句。例如ALL_ROWS。

1.2 提示的分类

(1) 指定优化方法和目标的提示

ALL_ROWS和FIRST_ROWS提示能够让你选择优化器的优化方法和目标。如果一个SQL语句中有一个提示指定了优化的方法和目标，那么优化器不会考虑是否存在统计数据、初始化参数OPTIMIZER_MODE的值和ALTER SESSION语句修改了OPTIMIZER_MODE的值，都会使用指定的路径。
如果你在SQL语句中指定了ALL_ROWS或者FIRST_ROWS(n)，并且访问表在数据字典中没有统计数据，那么优化器会使用默认统计值（如表中的存储分布）估算缺失的统计数据，并以此生成执行计划。估计出的统计数据没有用DBMS_STATS包生成的准，所以应该使用包收集统计数据。
如果你通过使用提示指定了访问途径或联接操作，并且指定了ALL_ROWS或者FIRST_ROWS(n)提示，那么优化器将优先考虑由提示指定的访问路径和连接操作。

数据库实例中有一个默认的目标设置，他指定了当执行一个查询时，要达到的目标是（1）快速响应，尽快的返回一些行-FIRST_ROWS；还是（2）牺牲前期处理时间以实现整体成本最小化-ALL_ROWS;
查看默认优化目标：

SQL> show parameter optimizer_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

默认查询示例：

SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

-----------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   13 |  1209 |    7 | 00:00:01 |
| * 1 |   HASH JOIN          |             |   13 |  1209 |    7 | 00:00:01 |
| * 2 |    TABLE ACCESS FULL | DEPARTMENTS |   13 |   273 |    3 | 00:00:01 |
|   3 |    TABLE ACCESS FULL | EMPLOYEES   |  115 |  8280 |    3 | 00:00:01 |
-----------------------------------------------------------------------------

默认使用了全表扫描。如果向要尽快的看到数据，可以使用FIRST_ROWS提示，改变执行计划，以尽快返回查询结果。
FIRST_ROWS查询示例：

SELECT/*+ FIRST_ROWS */ * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |   13 |  1209 |   11 | 00:00:01 |
|   1 |   NESTED LOOPS                      |                   |      |       |      |          |
|   2 |    NESTED LOOPS                     |                   |   13 |  1209 |   11 | 00:00:01 |
| * 3 |     TABLE ACCESS FULL               | DEPARTMENTS       |   13 |   273 |    3 | 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                   |      |       |      |          |
|   5 |      BITMAP AND                     |                   |      |       |      |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS |                   |      |       |      |          |
| * 7 |        INDEX RANGE SCAN             | EMP_MANAGER_IX    |   10 |       |    0 | 00:00:01 |
|   8 |       BITMAP CONVERSION FROM ROWIDS |                   |      |       |      |          |
| * 9 |        INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |   10 |       |    0 | 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID      | EMPLOYEES         |    1 |    72 |   11 | 00:00:01 |
--------------------------------------------------------------------------------------------------

(2) 启用优化器特性的提示

OPTIMIZER_FEATURES_ENABLE提示通常用来启用基于数据库版本号的优化器特性。使用这个提示的主要原因就是在数据库升级之后，某些查询的性能显著下降了，在找到新的解决方案之前，使用这个提示临时解决性能问题。
提示使用示例：

SELECT/*+ OPTIMIZER_FEATURES_ENABLE('10.2')*/ * FROM employees JOIN departments USING (department_id);

(3) 指定访问路径的提示

以下提示指示优化器按照指定的路径访问表：
FULL
CLUSTER
HASH
INDEX 和 NO_INDEX
INDEX_ASC 和 INDEX_DESC
INDEX_COMBINE 和 INDEX_JOIN
INDEX_JOIN
INDEX_FFS 和 NO_INDEX_FFS
INDEX_SS 和 NO_INDEX_SS
INDEX_SS_ASC 和 INDEX_SS_DESC

只有SQL语句的语法结构中的索引或簇有效时，指定的提示才会导致优化器选择指定的访问路径。如果指定的访问路径不可用，优化器会忽略它。如果语句为表使用别名，则在提示中使用别名而不是表名。如果模式名出现在语句中，则提示中的表名不应包含模式名。
常用的改变访问路径的提示-full：
不加提示：走了索引范围扫描

SELECT emp.employee_id, emp.first_name,emp.last_name
  FROM employees emp
 WHERE emp.department_id=20;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |    2 |    46 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |    2 |    46 |    2 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    2 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("EMP"."DEPARTMENT_ID"=20)

添加full提示：按照指定走全表扫描

SELECT/*+ full(emp) */ emp.employee_id, emp.first_name,emp.last_name
  FROM employees emp
 WHERE emp.department_id=20;

--------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |    2 |    46 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | EMPLOYEES |    2 |    46 |    3 | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("EMP"."DEPARTMENT_ID"=20)

常用的改变访问路径的提示-no_index：
添加no_index提示:指定不要使用表上指定的索引查询数据

SELECT/*+ no_index(emp EMP_DEPARTMENT_IX) */ emp.employee_id, emp.first_name,emp.last_name
  FROM employees emp
 WHERE emp.department_id=20;

--------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |    2 |    46 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | EMPLOYEES |    2 |    46 |    3 | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified