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

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.