何时在Oracle查询中使用提示 [英] When to use hints in oracle query
问题描述
我在网上浏览了一些文档，并且不鼓励使用提示.我对此仍然有疑问.提示在生产中真的很有用吗?特别是当数百个不同的客户使用相同的查询时.
I have gone through some documentation on the net and using hints is mostly discouraged. I still have doubts about this. Can hints be really useful in production specially when same query is used by hundreds of different customer.
仅当我们知道表中存在的记录数时，提示才有用吗?我在查询中使用了 leading ，当数据很大时，它会提供更快的结果，但是当获取的记录较少时，性能就不会那么好.
Is hint only useful when we know the number of records that are present in the tables? I am using leading in my query and it gives faster results when the data is very large but the performance is not that great when the records fetched are less.
David的 answer 很好，但是如果有人能澄清这一点，我将不胜感激详细信息.
This answer by David is very good but I would appreciate if someone clarified this in more details.
推荐答案
大多数提示是一种将我们的意图传达给优化器的方法.例如，您提到的leading提示表示按此顺序联接表.为什么这是必要的?通常是因为最佳连接顺序不明显，查询写得不好或数据库统计信息不正确.
Most hints are a way of communicating our intent to the optimizer. For instance, the leading hint you mention means join tables in this order. Why is this necessary? Often it's because the optimal join order is not obvious, because the query is badly written or the database statistics are inaccurate.
因此，诸如leading之类的提示的一种用法是找出最佳的执行路径，然后找出为什么数据库没有提示就不会选择该计划.收集最新的统计信息是否可以解决问题?重写FROM子句是否可以解决问题?如果是这样，我们可以删除提示并部署裸SQL.
So one use of hints such as leading is to figure out the best execution path, then to figure out why the database doesn't choose that plan without the hint. Does gathering fresh statistics solve the problem? Does rewriting the FROM clause solve the problem? If so, we can remove the hints and deploy the naked SQL.
有时候，我们有时无法解决这个难题，必须将提示保留在生产中.但是，这应该是一个罕见的例外.多年来，Oracle有很多非常聪明的人在基于成本的优化器上工作，因此它的决策通常比我们的决策要好.
Some times there are times where we cannot resolve this conundrum, and have to keep the hints in Production. However this should be a rare exception. Oracle have had lots of very clever people working on the Cost-Based Optimizer for many years, so its decisions are usually better than ours.
但是还有其他一些提示，我们在生产中不会眨眼. append通常对于调整批量插入至关重要. driving_site对于调整分布式查询至关重要.
But there are other hints we would not blink to see in Production. append is often crucial for tuning bulk inserts. driving_site can be vital in tuning distributed queries.
相反，其他提示几乎总是被滥用.是的，我在说你.盲目地放置/*+ parallel (t23, 16) */可能不会使查询的运行速度提高16倍，并且不会偶尔导致 的检索比单线程执行慢.
Conversely other hints are almost always abused. Yes parallel, I'm talking about you. Blindly putting /*+ parallel (t23, 16) */ will probably not make your query run sixteen times faster, and not infrequently will result in slower retrieval than a single-threaded execution.
因此，简而言之，对于何时应该使用提示并没有普遍适用的建议.关键是:
So, in short, there is no universally applicable advice to when we should use hints. The key things are:
- 了解数据库的工作方式，尤其是基于成本的优化器的工作方式；
- 了解每个提示的作用；
- 在与生产相当的数据的适当调优环境中测试提示的查询.
显然，最好的起点是 Oracle文档.但是，如果您想花一些钱，请参阅基于成本的优化程序是您可以做出的最好的投资.
Obviously the best place to start is the Oracle documentation. However, if you feel like spending some money, Jonathan Lewis's book on the Cost-Based Optimizer is the best investment you could make.
这篇关于何时在Oracle查询中使用提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
