英文:
How does a with clause effect the overall query
问题
我有一个查询，其中有一个类似如下的WITH子句，
select column1, column2,........., columnN
from table1, table2, table3, ......, tableN
where table2.uniqueIndex = table1.uniqueIndex
and table3.uniqueIndex = table2.uniqueIndex
and ...... tableN.uniqueIndex = tableN-1.uniqueIndex
在我的查询中，N = 23。
当单独运行这个查询时，运行时间约为1分钟。这是因为数据本身非常庞大，而且我没有应用任何过滤子句。
现在我想应用一个过滤子句，比如说table1.primaryKey = xxxxxxx，
当我将这个子句添加到查询中时，运行时间变为0.197秒，这很好。
然而，如果我将上述查询包装在一个WITH块中，然后从子查询中进行选择，然后应用我的过滤子句，运行时间超过3分钟。
在使用WITH语句时，Oracle是否会在添加我的过滤条件之前返回子查询的整个数据集？
我还了解到，Oracle会将WITH块保存为存储的查询，以便不必每次都运行它。
然而，我的表中的数据是不断变化且庞大的，所以我是否不应该使用WITH块来获取这些数据，然后应用我的过滤条件（用户输入）？
基本上，我的查询很慢，但不应该慢，尤其是当匹配我最重要的数据表之一的主键时。其他表都是小型配置表。
只是想了解WITH语句实际上如何改变效率，以及在使用WITH子句时有哪些"不要这样做"的规则（以及为什么？）。
感谢任何文档/教程。
英文:
I have a query that has a with clause like such,
select column1, column2,........., columnN
from table1, table2, table3, ......, tableN
where table2.uniqueIndex = table1.uniqueIndex
and table3.uniqueIndex = table2.uniqueIndex
and ...... tableN.uniqueIndex = tableN-1.uniqueIndex
Here in my query, N = 23.
When running this alone I get a runtime of about 1 minute. This is because is the data is very large itself and there is no filtering clauses that I have applied.
Now I want to apply a filter clause, say table1.primaryKey = xxxxxxx,
When I add this to the query I get a runtime of 0.197 secs which is nice.
However if I wrap the above query into a WITH block and then select from the subquery and then apply my filter clause, I get a runtime of more than 3 minutes.
Does oracle return the whole dataset of the subquery before it adds my filter when using the WITH statement.
Also I understand that oracle save WITH blocks as stored querys so as to not run it every time.
However, the data in my tables is everchanging and large, so should I not be using the WITH block to fetch this data and then apply my filters (user inputs)?
Essentially my query is slow, but it should not be slow especially when matching the primary key of one of my most important data table. The other tables are anyways small configuration tables.
Just want to understand how the WITH statement actually changes the efficiency and what are the 'never do' rules when using WITH clause (and why?).
Any documentation/tutorials is appreciated.
答案1
得分: 0
如前所建议，发布“快速”版本和“慢速”版本查询的EXPLAIN计划。并发布实际查询 - 那里可能有相关的细节。
作为一个盲猜测...您是否在两个查询中都使用了正确的数据类型？如果您的列是VARCHAR2，但在“过滤子句”谓词中使用的是数字（而不是字符串），那可能会阻止您期望的索引使用。
至于您对Oracle如何处理“WITH”子句的期望 - 它可能会将其材料化，也可能不会。Oracle按照自己的方式处理事情。计划应该会阐明这一点。
根据您的描述，使用WITH构造的查询应该与嵌入查询的查询一样表现出色。
英文:
As already suggested, post an EXPLAIN plan for both the "fast" version and the "slow" version of the query. And post the actual queries - there may be relevant detail there.
As a blind guess... are you using the correct datatypes in both of your queries? If your column is VARCHAR2 but you are using a number (not a string) in your "fliter clause" predicate, then that could prevent use of the indexes you expect.
As for your expectation of how Oracle handles the "WITH" clause - it may materialize it, or it may not. Oracle does things the way it wants to. The plans should shed some light on this.
From what you described, the query using the WITH construct should perform just as well as the one that has the predicate imbedded in the query.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论