Are Oracle Analytic Functions costly?

Question

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.

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?

Answer 1

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:

• Oracle: ROW_NUMBER vs ROWNUM

Oracle's optimizer knows about window functions and can use several tricks like STOPKEY and PUSHED RANK which make them more efficient.

The explain plan indicates a full table scan is performed on the table on which the oracle analytic function was used.

The table scan itself is not bad. This may be optimal indeed, if the TABLE ACCESS to retrieve the values missing in the index is more expensive than filtering and sorting.

Usually, if your table is indexed, the query WHERE and ORDER BY clauses allow using this index for ordering and optimizer considers this index worth using, the WINDOW BUFFER method is used for LAG and LEAD functions.

The engine just keeps a running buffer of 2 rows (or more, depending on the value of the offset) and returns the values from the first and the second row.

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.

Answer 2

Hi Mark,

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.

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.

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.

Answer 3

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.