大型查询、多个表、旧 JOIN 语法与新 JOIN 语法
我有一个大型查询，连接大约 20 个表（主要是外连接）。它使用带有逗号的旧连接语法和带有 (+) 的 where 条件进行外连接。 我们注意到它消耗了大量的服务器内存。我们正在尝试几件事，其中一个想法是将此查询转换为使用较新的 ANSI 语法，因为 ANSI 语法可以更好地控制 JOIN 的顺序，并且还可以在应用 JOIN 谓词时显式指定它们。
对于跨越大量表的大型查询，将查询从较旧的语法转换为较新的 ANSI 语法是否有助于减少处理的数据量？
I have a large query that joins around 20 tables (mostly outer joins). It is using the older join syntax with commas and where conditions with (+) for outer joins.
We noticed that it is consuming a lot of server memory. We are trying several things among which one idea is to convert this query to use the newer ANSI syntax, since the ANSI syntax allows better control on the order of JOINs and also specifies the JOIN predicates explicitly as they are applied.
Does converting the query from an older syntax to the newer ANSI syntax help in reducing the amount of data processed, for such large queries spanning a good number of tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据我的经验，它不会 - 它会生成相同的执行计划。也就是说，新的 JOIN 语法确实允许您执行旧语法无法执行的操作。出于这个原因，为了清晰起见，我建议将其转换。 ANSI 语法更容易阅读（至少对我来说）。转换后，您可以比较执行计划。
In my experience, it does not - it generates identical execution plans. That said, the newer JOIN syntax does allow you to things that you can't do with the old syntax. I would recommend converting it for that reason, and for clarity. The ANSI syntax is just so much easier to read (at least for me). Once converted you can then compare execution plans.
DCookie 涵盖了有关 ANSI 语法的所有内容。
然而，如果你外连接20个表，难怪你会消耗大量服务器内存。也许如果您将查询减少为较小的子查询，可能会提高性能。这样，并非所有表都必须在内存中读取，然后在内存中连接，然后过滤，然后仅选择您需要的列。
颠倒这个顺序至少会节省内存，尽管它不一定会提高执行速度。
DCookie said all there is to say about ANSI syntax.
However, if you outer join 20 tables, it is no wonder you will consume a lot of server memory. Maybe if you cut down your query in smaller subqueries it might improve performance. That way not all tables have to be read in memory and then joined in memory and then filtered and then only the columns you need selected.
Reversing this order will at least save memory, although it doesn't have to improve execution speed.
正如 DCookie 提到的，两个版本应该产生相同的执行计划。我首先查看当前查询的执行计划并找出实际占用内存的内容。快速查看 DBMS_XPLAN.DISPLAY_CURSOR 输出应该是一个好的开始。一旦您确切地知道您要尝试改进查询的哪一部分，您就可以分析切换到 ANSI 样式连接是否会帮助您实现最终目标。
As DCookie mentioned, both versions should produce identical execution plans. I would start by looking at the current query's execution plan and figuring out what is actually taking up the memory. A quick look at DBMS_XPLAN.DISPLAY_CURSOR output should be a good start. Once you know exactly what part of the query you are trying to improve, then you can analyze if switching to ANSI style joins will do anything to help you reach your end goal.