Oracle 分析功能是否昂贵？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1467018/
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
Are Oracle Analytic Functions costly?
提问by onejigtwojig
Has anyone encountered slow performance when using oracle analytic functions? The lead() oracle analytic function was used to generate a new field in the table. Basically, it would allow the previous row's field value to be used as the value of the current row's new field. The explain plan indicates a full table scan is performed on the table on which the oracle analytic function was used.
有没有人在使用 oracle 分析函数时遇到性能下降的问题？使用lead() oracle 分析函数在表中生成一个新字段。基本上，它允许将前一行的字段值用作当前行的新字段的值。解释计划表明对使用 oracle 分析函数的表执行了全表扫描。
To avoid the cost of this full table scan, I may have to just manually populate a concrete field with the previous row's value using a after insert/update trigger
为了避免这种全表扫描的成本，我可能只需要使用插入/更新后触发器手动填充具有前一行值的具体字段
Has anyone decided not to use an oracle analytic function because of its high cost? Should oracle analytic functions be seldom used?
有没有人因为成本高而决定不使用oracle分析功能？oracle 分析函数应该很少使用吗？
回答by Vincent Malgrat
the analytical functions are not without a cost: they have to store data for intermediate results (running totals, windowing functions...) which needs memory and they also take some processing power. Some functions will need to get to the last row of a result set to be able to return a result (MIN/MAX for example). Most functions will also have an implicit SORT operation.
分析函数并非没有成本：它们必须存储中间结果的数据（运行总计、窗口函数......），这需要内存，并且还需要一些处理能力。某些函数需要到达结果集的最后一行才能返回结果（例如 MIN/MAX）。大多数函数还将具有隐式 SORT 操作。
They are therefore not free in terms of resources but they are SET operations and most of the time they are therefore a lot more efficient than writing custom row-by-row plsql procedure or traditional SQL.
因此，它们在资源方面不是免费的，但它们是 SET 操作，因此在大多数情况下，它们比编写自定义的逐行 plsql 过程或传统 SQL 高效得多。
You'll have to compare and benchmark in your specific case but if you use them wisely, you will see that they are a powerful performance tool, not a hindrance.
您必须在特定情况下进行比较和基准测试，但如果您明智地使用它们，您会发现它们是一种强大的性能工具，而不是障碍。
回答by Gary Myers
Some detail on this is available at Jonathan Lewis's blog here.
乔纳森刘易斯的博客在这里提供了一些细节。
Really the question should be, are they more or less costly than the alternative, and that will come down to the particular situation. In some cases you may prefer to pull the data to an app server and process it there just because it is generally cheaper/easier to have extra hardware at that level.
真正的问题应该是，它们是否比替代方案成本更高或更低，这将归结为特定情况。在某些情况下，您可能更喜欢将数据拉到应用程序服务器并在那里进行处理，因为在该级别拥有额外的硬件通常更便宜/更容易。
But given a choice between doing it in the SQL and adding PL/SQL processing, I'd generally use the SQL.
但是如果在 SQL 中执行它和添加 PL/SQL 处理之间进行选择，我通常会使用 SQL。
回答by Quassnoi
This depends on how your table is indexed and what functions do you use.
这取决于您的表的索引方式以及您使用的函数。
ROW_NUMBER(), for instance, seems to be less efficient than ROWNUM, even if the indexes are used. See this article in my blog for performance comparison:
ROW_NUMBER()例如，ROWNUM即使使用了索引，似乎也比 效率低。有关性能比较，请参阅我博客中的这篇文章：
Oracle's optimizer knows about window functions and can use several tricks like STOPKEYand PUSHED RANKwhich make them more efficient.
Oracle的优化器了解窗口函数，并且可以使用诸如STOPKEY和 之类的几种技巧PUSHED RANK来提高它们的效率。
The explain plan indicates a full table scan is performed on the table on which the oracle analytic function was used.
解释计划表明对使用 oracle 分析函数的表执行了全表扫描。
The table scan itself is not bad. This may be optimal indeed, if the TABLE ACCESSto retrieve the values missing in the index is more expensive than filtering and sorting.
表扫描本身还不错。如果TABLE ACCESS检索索引中缺失的值比过滤和排序更昂贵，这可能确实是最佳的。
Usually, if your table is indexed, the query WHEREand ORDER BYclauses allow using this index for ordering and optimizer considers this index worth using, the WINDOW BUFFERmethod is used for LAGand LEADfunctions.
通常，如果你的表被索引，查询WHERE和ORDER BY子句允许使用这个索引进行排序，优化器认为这个索引值得使用，WINDOW BUFFER方法用于LAG和LEAD函数。
The engine just keeps a running buffer of 2rows (or more, depending on the value of the offset) and returns the values from the first and the second row.
引擎只保留2行（或更多，取决于偏移量的值）的运行缓冲区，并返回第一行和第二行的值。
However, the optimizer can consider the index not worth using at all.
但是，优化器可以认为该索引根本不值得使用。
In this case, it will use WINDOW SORT: same thing but the sorting is done in memory or temporary tablespace.
在这种情况下，它将使用WINDOW SORT: 相同的东西，但排序是在内存或临时表空间中完成的。
回答by Nathan
Of course, they have a cost and you've got to decide whether you're able to pay it or not.
当然，他们有成本，你必须决定你是否能够支付。
In my case, I created a stored procedure that iterates a table and calculates some dates using Oracle lead() function and store the results in another table. Finally, I use this later table in my app and update the first table (running the stored procedure) once a week because that data doesn't change frequently.
在我的例子中，我创建了一个存储过程，它迭代一个表并使用 Oracle Lead() 函数计算一些日期并将结果存储在另一个表中。最后，我在我的应用程序中使用这个后面的表并每周更新一次第一个表（运行存储过程），因为该数据不会经常更改。
For me, this was the best solution.
对我来说，这是最好的解决方案。
