gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-03 12:44:04 26 4
gpt4 key购买 nike

我试图优化查询性能,不得不使用优化器提示。但是我从未听说过优化器一次不会使用多个提示。

例如

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列上。

谢谢,

最佳答案

尝试在单个注释块中指定所有提示,如精彩的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


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;

关于sql - Oracle优化器会在同一SELECT中使用多个提示吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/421973/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com