SQL Oracle 优化器会在同一个 SELECT 中使用多个 Hints 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/421973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Will Oracle optimizer use multiple Hints in the same SELECT?
提问by Stew S
I'm trying to optimize query performance and have had to resort to using optimizer hints. But I've never learned if the optimizer will use more than one hint at a time.
我正在尝试优化查询性能，不得不求助于使用优化器提示。但是我从来没有知道优化器是否会一次使用多个提示。
e.g.
例如
SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
/*+ LEADING(i vol) */
/*+ ALL_ROWS */
i.id_number,
...
FROM i_table i
JOIN vol_table vol on vol.id_number = i.id_number
JOIN to_a_bunch_of_other_tables...
WHERE i.solicitor_id = '123'
AND vol.solicitable_ind = 1;
The explain plan shows the same cost, but I know that's just an estimate.
解释计划显示了相同的成本，但我知道这只是一个估计。
Please assume that all table and index statistics have been calculated. FYI, the index dcf_vol_prospect_ids_idx is on the i.solicitor_id column.
请假设所有表和索引统计信息都已计算。仅供参考，索引 dcf_vol_prospect_ids_idx 在 i.solicitor_id 列上。
Thanks,
谢谢，
Stew
炖
回答by Dave Costa
Try specifying all the hints in a single comment block, as shown in this example from the wonderful Oracle documentation (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm).
尝试在单个注释块中指定所有提示，如精彩的 Oracle 文档 ( http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm) 中的示例所示。
16.2.1 Specifying a Full Set of Hints
When using hints, in some cases, you might need to specify a full set of hints in order to ensure the optimal execution plan. For example, if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer might not necessarily use that hint, because the optimizer might have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer.
In Example 16-1, the LEADING hint specifies the exact join order to be used; the join methods to be used on the different tables are also specified.
Example 16-1 Specifying a Full Set of Hints
16.2.1 指定全套提示
使用提示时，在某些情况下，您可能需要指定完整的提示集以确保最佳执行计划。例如，如果您有一个非常复杂的查询，它由许多表连接组成，并且如果您只为给定的表指定 INDEX 提示，那么优化器需要确定要使用的剩余访问路径，以及相应的访问路径加入方法。因此，即使您提供了 INDEX 提示，优化器也不一定会使用该提示，因为优化器可能已经确定由于优化器选择的连接方法和访问路径而无法使用请求的索引。
在例 16-1 中，LEADING 提示指定要使用的确切连接顺序；还指定了用于不同表的连接方法。
示例 16-1 指定一整套提示
SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)
USE_MERGE(j) FULL(j) */
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, employees e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id ORDER BY total_sal;
回答by Stew S
In fact, the recommendation of Jonathan Lewis, Author of Cost-Based Oracle Fundamentals is that if the CBO fails at finding the correct plan, you need to take over the job of the CBO and "layer-in" the hints - an average of two hints per table in the query.
事实上，Cost-Based Oracle Fundamentals 的作者乔纳森刘易斯的建议是，如果 CBO 未能找到正确的计划，你需要接管 CBO 的工作并“分层”暗示——平均查询中的每个表有两个提示。
The reason is that one hint could lead to yet another bad and possibly even worse plan than the CBO would get unaided. If the CBO is wrong, you need to give it the whole plan, not just a nudge in the right direction.
原因是，一个暗示可能会导致另一个糟糕的甚至可能比 CBO 没有帮助的更糟糕的计划。如果 CBO 错了，你需要给它整个计划，而不仅仅是在正确方向上的推动。
回答by Lukasz Szozda
Oracle 19c introduced Hint Usage Reporting feature:
Oracle 19c 引入了提示使用情况报告功能：
EXPLAIN PLAN FOR
SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
/*+ LEADING(i vol) */
/*+ ALL_ROWS */
i.id_number,
...
FROM i_table i
JOIN vol_table vol on vol.id_number = i.id_number
JOIN to_a_bunch_of_other_tables...
WHERE i.solicitor_id = '123'
AND vol.solicitable_ind = 1;
SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));
--============
It shows another section Hint Report:
它显示了另一部分Hint Report：
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: ...
---------------------------------------------------
...
