Oracle中的SQL优化 [英] SQL Optimization in Oracle

问题描述

我们正在使用Oracle 11，最近我购买了Dell SQL Optimizer(Xpert Toad软件包随附).今天早上，我们有一条声明要花比正常运行更长的时间，而在最终使它运行(创建时缺少某些条件)之后，我很好奇，以前从未使用过任何SQL优化器，它将更改为.它返回了同一条语句的150多个变体，但是成本最低的那个简单地添加到了下面的行中.

We are using Oracle 11 and I recently acquired a Dell SQL Optimizer (included with the Xpert Toad package). We had a statement this morning that was taking longer than normal to run, and after we eventually got it running (missing some conditions from when it was created) I was curious, having never used any SQL optimizer before, what it would change it to. It came back with over 150 variations of the same statement, but the one with the lowest cost simply added to the following line.

AND o.curdate > 0 + UID * 0

我们已经有o.curdate> 0，并添加了"+ UID * 0".这将运行时间从一分钟以上减少到了3秒.我以为它与Oracle如何转换和处理条件有关，但是我很好奇是否有任何Oracle专家可以提供一些见解，使这种大于零的检查如何将运行时间减少15倍. .谢谢！

We already had o.curdate > 0, and the "+ UID * 0" was added. This decreased the runtime from over a minute to 3 seconds. I assume it has something to do with how Oracle translates and processes the conditions, but I was curious if any of the Oracle gurus would be able to provide some insight as to how this addition to the greater than zero check decreased the runtime by 15 times. Thanks!

推荐答案

UID * 0用于对优化器隐藏0.优化器将使用其统计数据来确定对o.curdate > 0使用索引扫描是否有意义.只要优化器知道o.curdate > value中的值，它就会这样做.但是，当值未知时(在这里，因为函数UID将在执行时被调用并以某种方式数学化到该值中)，优化器无法预见可以访问的行的百分比，因此选择了最佳的最佳访问方法.

The UID * 0 is used to hide the 0 from the optimizer. The optimizer would use its statistic data to find out whether using an index scan on o.curdate > 0 makes sense. As long as the optimizer knows the value in o.curdate > value it will do so. But when the value is unknown (here because the function UID will be called on execution and somehow mathed into the value), the optimizers cannot foresee what percentage of rows may be accessed and thus choses an avarage best access method.

示例:您有一个ID为1到100的表.询问ID> 0将导致全表扫描，而询问ID> 99可能导致索引范围扫描.当请求ID> 0 + UID * 0时，突然会使优化器对该值视而不见，它可能选择索引计划而不是全表扫描.

Example: You have a table with IDs 1 to 100. Asking for ID > 0 will result in a full table scan, whereas asking for ID > 99 will likely result in an index range scan. When asking for ID > 0 + UID * 0 suddenly makes the optimizer blind to the value, and it may chose the index plan rather then full table scan.

这篇关于Oracle中的SQL优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！