oracle 内联视图上的谓词推送如何减慢查询速度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1044584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How could predicate pushing on an inline view slow down a query?
提问by jnt30
I have inherited a somewhat messy query that I am working on refactoring to be performant.
我继承了一个有点凌乱的查询，我正在重构以提高性能。
During this process, one of the things I did due to personal preference was change all of the ANSI-99 join syntax from the "inner join" and "left outer join" statements to be predicates in the query. I noticed two very strange things that I would appreciate an explanation on.
在此过程中，我根据个人喜好所做的一件事是将所有 ANSI-99 连接语法从“内连接”和“左外连接”语句更改为查询中的谓词。我注意到两件非常奇怪的事情，我希望得到解释。
- Changing the joins from the "INNER JOIN..." syntax has changed the explain plan. With the ANSI 99 syntax, oracle did full table scans on the columns that were being joined. After changing the join syntax, it now does predicate pushing. Why would the join syntax change the explain plan?
- The predicate pushing on the inline view has actually slowed down the query by a very significant margin. The query that was running (prior to altering the joins) at around 3 sec. Now it is taking 9 sec. To be honest, I fairly new to reading explain plans so it's entirely possible that the restructuring of the query has slowed it down for a different reason. But ultimately my question is: "Is it possible for predicate pushing on indexed columns to slow down a query so substantially? If so, why?"
- 从“INNER JOIN...”语法更改连接已更改解释计划。使用 ANSI 99 语法，oracle 对正在连接的列进行全表扫描。更改连接语法后，它现在执行谓词推送。为什么连接语法会改变解释计划？
- 推入内联视图的谓词实际上大大减慢了查询速度。在 3 秒左右运行的查询（在更改连接之前）。现在需要 9 秒。老实说，我对阅读解释计划还很陌生，因此完全有可能由于不同的原因，查询的重组减慢了速度。但最终我的问题是：“谓词推动索引列是否有可能大幅减慢查询速度？如果是这样，为什么？”
Thanks for the replies, and my apologies if this isn't very clear...
感谢您的回复，如果不是很清楚，我很抱歉...
回答by Quassnoi
Is it possible for predicate pushing on indexed columns to slow down a query so substantially? If so, why?
谓词推送索引列是否有可能大幅减慢查询速度？如果是这样，为什么？
Sure it is.
就是这样。
As a rule, predicate pushing makes the optimizer to choose NESTED LOOPSinstead of HASH JOIN.
通常，谓词推送使优化器选择NESTED LOOPS而不是HASH JOIN。
This can be slower if the condition is not selective.
如果条件不是选择性的，这可能会更慢。
This query
这个查询
SELECT *
FROM table1, t1
(
SELECT /*+ NO_PUSH_PRED */
*
FROM table2 t2
WHERE t2.col1 = :value1
) t2o
WHERE t2o.col2 = t1.col2
most probably will build a hash table over the contents of table1and will probe the rows returned by the view against this hash table (or vice versa).
很可能会在其内容上构建一个哈希表table1，并将针对该哈希表探测视图返回的行（反之亦然）。
This query:
这个查询：
SELECT *
FROM table1, t1
(
SELECT /*+ PUSH_PRED */
*
FROM table2 t2
WHERE t2.col1 = :value1
) t2o
WHERE t2o.col2 = t1.col2
will use the NESTED LOOPSand an index on (t2.col1, t2.col2)if it's defined.
如果已定义，将使用NESTED LOOPS和 索引(t2.col1, t2.col2)。
The latter is more efficient if col2is selective on table2, and less efficient if it's not.
如果col2是选择性的table2，则后者效率更高，如果不是，则效率较低。
My educated guess is that is exactly what's happening in your case.
我有根据的猜测是，这正是你的情况。
If you post your queries and execution plans, I probably will be able to tell more.
如果你发布你的查询和执行计划，我可能会告诉你更多。
