Oracle数据库Hint大全，31个使用案例，速来下载！

原创陈举超 2025-04-16

837

图片.png

一:Oracle Hints介绍

Hints是SQL语句中的注释，用于将指令传递给Oracle数据库优化器。优化器使用这些Hints为语句选择执行计划，除非存在阻止优化器这样做的条件。
Oracle7中引入了Hints，当优化器生成次优计划时，用户几乎没有追索权。现在，Oracle提供了许多工具，包括SQL调优顾问( SQL Tuning Advisor)、SQL计划管理(SQL plan management)和SQL性能分析器(SQL Performance Analyzer)，以帮助您解决优化器无法解决的性能问题。Oracle强烈建议您使用这些工具而不是Hints。这些工具远远优于Hints，因为当持续使用时，它们会随着数据和数据库环境的变化提供新的解决方案。
Hints应谨慎使用，只有在您收集了相关表的统计数据并使用EXPLAIN plan语句在没有Hints的情况下评估了优化器计划之后。更改数据库条件以及后续版本中的查询性能增强可能会对代码中的Hints如何影响性能产生重大影响。
本节的其余部分提供了一些常用Hints的信息。如果您决定使用Hints而不是更高级的调优工具，请注意，使用Hints带来的任何短期好处可能不会长期持续带来性能的提高。

二:使用 Hints

一个语句块只能有一个包含Hints的注释，并且该注释必须跟在SELECT、UPDATE、INSERT、MERGE或DELETE关键字后面。
以下语法图显示了Oracle在语句块中支持的两种注释样式中包含的Hints。Hints语法必须紧跟在语句块开头的INSERT、UPDATE、DELETE、SELECT或MERGE关键字之后。
图片.png
where:
加号（+）使Oracle将注释解释为Hints列表。加号必须紧跟在注释分隔符之后。不允许有任何空间。
Hints是本节讨论的Hints之一。加号和Hints之间的空格是可选的。如果注释包含多个Hints，则用至少一个空格分隔Hints。
string是其他可以穿插Hints的注释文本。

三:按功能类别Hints

表2-23按功能类别列出了Hints，并包含了对每个Hints的语法和语义的交叉引用。
下表按字母顺序列出了Hints。
图片.png

1.ALL_ROWS Hint

ALL_ROWS提示指示优化器以最佳吞吐量为目标优化语句块，即最小的总资源消耗。例如，优化器使用查询优化方法来优化此语句以获得最佳吞吐量：

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE employee_id = 107;

2.CONTAINERS Hint

SELECT /*+ CONTAINERS(DEFAULT_PDB_HINT='NO_PARALLEL') */
  (CASE WHEN COUNT(*) < 10000
        THEN 'Less than 10,000'
        ELSE '10,000 or more' END) "Number of Tables"
  FROM CONTAINERS(DBA_TABLES);

3.DRIVING_SITE Hint

SELECT /*+ DRIVING_SITE(departments) */ * 
  FROM employees, departments@rsite 
  WHERE employees.department_id = departments.department_id;

4.DYNAMIC_SAMPLING Hint

整数值为0到10，表示采样程度。
如果表已经存在cardinality statistic，则优化器会使用它。否则，优化器会启用动态采样来估计基数统计。

SELECT /*+ DYNAMIC_SAMPLING(e 1) */ count(*)
  FROM employees e;

SELECT /*+ DYNAMIC_SAMPLING(employees 1) */ *
  FROM employees 
  WHERE ...

5.FIRST_ROWS Hint

FIRST_ROWS提示指示Oracle优化单个SQL语句以实现快速响应，选择最有效地返回前n行的计划。对于整数，指定要返回的行数。
例如，优化器使用查询优化方法来优化以下语句以获得最佳响应时间：

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE department_id = 20;

6.FULL Hint

FULL提示指示优化器对指定表执行全表扫描。例如：

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM hr.employees e 
  WHERE last_name LIKE :b1;

7.GROUPING Hint

SELECT PREDICTION(/*+ GROUPING */my_model USING *) pred FROM <input table>;

8.INDEX Hint

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
  FROM employees 
  WHERE department_id > 50;

9.INDEX_ASC Hint

INDEX_ASC提示指示优化器对指定表使用索引扫描。如果语句使用索引范围扫描，则Oracle数据库会按索引值的升序扫描索引条目。每个参数的作用与INDEX提示中的相同。
范围扫描的默认行为是按索引值的升序扫描索引条目，或按降序扫描索引条目。此提示不会更改索引的默认顺序，因此仅指定index提示。但是，如果默认行为发生变化，您可以使用INDEX_ASC提示显式指定升序范围扫描。

10.INDEX_DESC Hint

INDEX_DESC提示指示优化器对指定表使用降序索引扫描。如果语句使用索引范围扫描，并且索引是升序的，则Oracle会按索引值的降序扫描索引条目。在分区索引中，结果在每个分区内按降序排列。对于降序索引，此提示有效地取消了降序，导致按升序扫描索引条目。每个参数的作用与INDEX提示中的相同。例如：

SELECT /*+ INDEX_DESC(e emp_name_ix) */ *
  FROM employees e;

11.INDEX_COMBINE Hint

INDEX_COMBINE提示可以使用任何类型的索引：bitmap, b-tree或domain。
如果不在INDEX_COMBINE提示中指定indexspec，优化器将使用尽可能多的索引隐式地将INDEX提示应用于所有索引。
如果指定indexspec，则优化器将使用所有合法有效的提示索引，而不管成本如何。
每个参数的作用与INDEX提示中的相同。例如：

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

12.INDEX_FFS Hint

INDEX_FFS提示指示优化器执行fast full index scan，而不是full table scan。
每个参数的作用与INDEX提示中的相同。例如：

SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name
  FROM employees e;

13.INDEX_JOIN Hint

INDEX_JOIN提示指示优化器使用index join索引联接作为访问路径。
为了使提示产生积极的效果，必须存在足够少的索引，其中包含解析查询所需的所有列。

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
  FROM employees e
  WHERE manager_id < 110
AND department_id < 50;

14.INDEX_SS Hint

INDEX_SS提示指示优化器对指定表执行索引跳过扫描index skip scan。
如果语句使用索引范围扫描，则Oracle会按索引值的升序扫描索引条目。
在分区索引中，结果在每个分区内按升序排列。

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';
INDEX_SS_ASC Hint

相同提示:INDEX_SS_ASC Hint

15.INDEX_SS_DESC Hint

SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';

16.INMEMORY Hint

INMEMORY提示启用In-Memory查询。
此提示不会指示优化器执行全表扫描。

17.LEADING Hint

LEADING提示是一个多表提示，可以指定多个表或视图。
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;

18.MERGE Hint

MERGE提示允许您合并查询中的视图。
如果视图的查询块在SELECT列表中包含GROUP BY子句或DISTINCT运算符，则只有启用了复杂视图合并，优化器才能将视图合并到访问语句中。
如果IN子查询不相关，也可以使用复杂合并将其合并到访问语句中。

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
   FROM employees e1,
        (SELECT department_id, avg(salary) avg_salary 
           FROM employees e2
           GROUP BY department_id) v 
   WHERE e1.department_id = v.department_id
     AND e1.salary > v.avg_salary
   ORDER BY e1.last_name;

19.NO_INDEX Hint

SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id 
  FROM employees 
  WHERE employee_id > 200;

其他 NO 相关的 Hint如下:
NO_INDEX_FFS Hint ，NO_INDEX_SS Hint ，NO_INMEMORY Hint，NO_MERGE Hint ，NO_PARALLEL Hint ，NO_PUSH_PRED Hint ，NO_REWRITE Hint ，NOREWRITE Hint，NO_STAR_TRANSFORMATION Hint ，NO_UNNEST Hint ，NO_USE_BAND Hint ，NO_USE_HASH Hint ，NO_USE_MERGE Hint ，NO_USE_NL Hint。

20.OPT_PARAM Hint

OPT_MAM提示允许您仅在当前查询的持续时间内设置初始化参数。此提示仅对以下参数有效：
APPROX_FOR_AGGREGATION, APPROX_FOR_COUNT_DISTINCT, APPROX_FOR_PERCENTILE, OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED.
例如:

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ *
  FROM ... ;

21.ORDERED Hint

ORDERD提示指示Oracle按照表在FROM子句中出现的顺序连接表。
Oracle建议您使用LEADING提示，它比ORDERD提示更通用。
当您在需要连接的SQL语句中省略ORDERD提示时，优化器会选择连接表的顺序。
如果您知道优化器不知道的关于从每个表中选择的行数的信息，您可能希望使用ORDERD提示指定连接顺序。
这些信息使您能够比优化器更好地选择内部和外部表。

SELECT  /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
  FROM customers c, order_items l, orders o
  WHERE c.cust_last_name = 'Taylor'
    AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

22.PARALLEL Hint

从Oracle Database 11g Release 2开始，PARALLEL和NO_PARALLEL提示是语句级提示，并取代了早期的对象级提示：PARALLE_INDEX、NO_PARALLE_INDEX以及之前指定的PARALLER和NO_PARLLEL提示。对于PARALLEL，如果指定整数，则该并行度将用于语句。如果省略整数，则数据库会计算并行度。所有可以使用并行性的访问路径都将使用指定或计算的并行度。

SELECT /*+ PARALLEL */ last_name
  FROM employees;

SELECT /*+ PARALLEL (AUTO) */ last_name
  FROM employees;

CREATE TABLE parallel_table (col1 number, col2 VARCHAR2(10)) PARALLEL 5; 

SELECT /*+ PARALLEL (MANUAL) */ col2
  FROM parallel_table;

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name
  FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name
  FROM employees hr_emp;

23.PARALLEL_INDEX Hint

SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */
PARALLE_INDEX提示指示优化器使用指定数量的并发服务器来并行化分区索引的索引范围扫描index range scans、full scans和fast full scans。

24.PUSH_PRED Hint

PUSH_PRED提示指示优化器将联接谓词推入视图。例如：

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
  FROM employees e,
    (SELECT manager_id
      FROM employees) v
  WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;

25.STAR_TRANSFORMATION Hint

STAR_TRANSFORMATION提示指示优化器使用已使用转换的最佳计划。如果没有提示，优化器可以做出查询优化决策，使用在没有转换的情况下生成的最佳计划，而不是转换后的查询的最佳计划。例如：

SELECT /*+ STAR_TRANSFORMATION */ s.time_id, s.prod_id, s.channel_id
  FROM sales s, times t, products p, channels c
  WHERE s.time_id = t.time_id
    AND s.prod_id = p.prod_id
    AND s.channel_id = c.channel_id
AND c.channel_desc = 'Tele Sales';

26.USE_BAND Hint

USE_BAND提示指示优化器使用带连接将每个指定的表与另一个行源连接起来。例如：

SELECT /*+ USE_BAND(e1 e2) */
  e1.last_name
  || ' has salary between 100 less and 100 more than '
  || e2.last_name AS "SALARY COMPARISON"
FROM employees e1, employees e2
WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100;

27.USE_CONCAT Hint

USE_CONCAT提示指示优化器使用UNION ALL集运算符将查询WHERE子句中的组合OR条件转换为复合查询。如果没有这个提示，只有当使用连接的查询成本比没有连接的成本便宜时，才会发生这种转换。USE_CONCAT提示覆盖了成本考虑。例如：

SELECT /*+ USE_CONCAT */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

28.USE_HASH Hint

USE_HASH提示指示优化器使用哈希连接将每个指定的表与另一个行源连接起来。例如：

SELECT /*+ USE_HASH(l h) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
AND l.order_id > 2400;

29.USE_MERGE Hint

SELECT /*+ USE_MERGE(employees departments) */ * 
  FROM employees, departments 
  WHERE employees.department_id = departments.department_id;

30.USE_NL Hint

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id;

USE_NL提示中列出表的顺序没有指定联接顺序。要提示特定的连接顺序，需要LEADING提示。

select /*+ LEADING(t2) USE_NL(t1) */ sum(t1.a),sum(t2.a)
from   t1 , t2
where   t1.b = t2.b;
select * from table(dbms_xplan.display_cursor()) ;

31.USE_NL_WITH_INDEX Hint

USE_NL_WITH_INDEX提示指示优化器使用指定表作为内部表，通过嵌套循环连接将指定表连接到另一个行源。例如：

SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
AND l.order_id > 2400;

参考链接:

详细内容，见下面链接:
Database/Oracle/Oracle Database/Release 19/SQL Language Reference/2 Basic Elements of Oracle SQL/Comments

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E

###chenjuchao 20250416###
欢迎关注我的公众号《IT小Chen》

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

关注作者