什么时候使用Oracle提示? [英] When to use Oracle hints?

问题描述

我正在对Oracle模式(Oracle版本10)进行一些重构，并且我看到很多使用提示*+ALL_ROWS*/的视图.在其他视图中，还存在其他类型的提示. 为什么我应该使用提示?数据库不是基于查询的最佳选择吗?非常感谢！

I'm doing some refactoring on a Oracle Schema (oracle version 10), and I see a lot of views that use hints *+ALL_ROWS*/. In others views there are also other type of hints. Why I should use a hint? Doesn't the DB make the best choice in base of the query? many thanks!

推荐答案

这是一个很好的问题，但是没有一个唯一的答案，因为有不同类别的提示适用于不同的建议. http://docs.oracle.com/cd/E11882_01 /server.112/e16638/hintsref.htm#PFGRF501

That's a good question, but there's no single answer to it because there are different categories of hint for which different advice would apply. http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF501

ALL_ROWS是一种优化方法，为了清楚地表明您的目标是尽早获取结果集的最后一行而不是第一行，对其进行指定完全有效.在许多情况下，无论如何，优化器都会从查询中推断出这一点，因此它可能是多余的，但是您不会通过正确使用它来损害任何东西.

ALL_ROWS is an optimisation approach, and it's perfectly valid to specify it in order to make it clear that your goal is to get the last row of the result set as early as possible, not the first. In many cases the optimiser will deduce that from the query anyway, so it may be redundant, but you're not going to harm anything by using it correctly.

然后有不同的类别，其中一些类别可能被表征为用于测试和探索，例如optimizer_features_enable.可以说，影响连接顺序，访问路径和连接操作的提示属于这种类型，因为有时不建议在应用程序中使用它们.但是，优化器不是完美的，并且没有完美的信息，有时它会基于需要纠正的不完整信息做出选择.

Then there are different categories, some of which might be characterised as being for testing and exploration, such as the optimizer_features_enable. Arguably the hints that affect join order, access path, and join operations are of this type as they're sometimes discouraged for use in applications. However the optimizer is not perfect, and does not have perfect information, and sometimes it will make a choice based on incomplete information that needs to be corrected.

某些提示无疑是有用且适当的-APPEND可能是最好的示例，因为它是调用直接路径插入的标准方法.

Some hints are unquestionably useful and appropriate -- APPEND is possibly the best example, as it is the standard method for invoking direct path insert.

最后，很难对此给出一般性建议.实际上，每个提示都需要在是否应在生产代码中使用的方面得到解决，但是如果您了解优化器并了解您实际考虑的提示以及是否有更好的替代方法(例如，更好的统计信息，不同方法)初始化参数或动态采样(本身就是一个提示)-您将能够进行自己的评估并在遇到挑战时进行辩护.

In the end it's really difficult to give generalised advice on this. Really every hint needs to be addressed in respect of whether it should be used in production code or not, but if you understand the optimiser and understand what the hints you are considering really do and whether there are better alternatives -- eg better statistics, different init parameters, or dynamic sampling (itself a hint) -- the you'll be able to make your own assessment and defend it if challenged.

这篇关于什么时候使用Oracle提示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！