Oracle 优化器会在同一个 SELECT 中使用多个提示吗？

发布于 2024-07-12 09:57:25 字数 544 浏览 12 评论 0原文

我正在尝试优化查询性能，并且不得不求助于使用优化器提示。但我从来不知道优化器是否会一次使用多个提示。

例如，

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;

解释计划显示了相同的成本，但我知道这只是一个估计。

请假设所有表和索引统计信息均已计算。仅供参考，索引 dcf_vol_prospect_ids_idx 位于 i.solicitor_id 列上。

谢谢，

炖

原文

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.

Thanks,

Stew

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

婴鹅 2024-07-19 09:57:25

尝试在单个注释块中指定所有提示，如精彩的 Oracle 文档 (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm）。

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;

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).

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

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;

回复收藏 0 原文

二货你真萌 2024-07-19 09:57:25

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'));
                                                     --============

它显示了另一部分提示报告：

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: ...
---------------------------------------------------
...

Oracle 19c introduced Hint Usage Reporting feature:

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 (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: ...
---------------------------------------------------
...

回复收藏 0 原文