CODEWITHSUNDEEP
oracle-database
Mistre83
Mistre83

Reputation: 2827

Oracle performance with function in select

In my production enviroment i have a significative difference when i call a function directly in select and when i "wrap" the select and then call then function outside (below i show you two simple example)

In the first case (when the function is called directly) the result query end in 35 seconds.

In the second case (function wrapped) the result end in 7 seconds.

Sure, the query is exatcly the same and the result rows are the same too.

There is a difference (in performance) calling the same function in the two ways?

SLOW VERSION:

select col1, col2, myFun(col3)
  from aTable;

FAST VERSION

select col1, col2, myFun(col3)
  from (select col1, col2, col3
          from aTable);

Upvotes: 2

Views: 6160

Answers (2)

Kai
Kai

Reputation: 708

According to https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#scalar-subquery-caching Oracle will also cache the results of the function if used as a subquery. So I guess it is the combination of the fact that

  • the function is only applied for the real number of records minimizing the context switches
  • that results are cached

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

Suppose there are 1 million rows in aTable, but your real query has a WHERE clause that returns only 10 rows:

select col1, col2, myFun(col3)
  from aTable
  join ...
  join ...
 where ...;

Oracle may decide to call the function myFun before joining and filtering, so it will be called 1 million times. Calling PL/SQL functions from SQL is slow due to context switching.

When you wrap it like this:

select col1, col2, myFun(col3)
  from (select col1, col2, col3
          from aTable
          join ...
          join ...
          where ...
       );

... now Oracle is probably going to perform the joins and filtering first, get 10 rows back, and then call myFun 10 times.

If each function call takes 0.001 seconds, then this will take 1,000,000*0.001 = 1,000 seconds for the first query, and 10*0.001 = 0.01 seconds for the second wrapped query.

Note that I said may and probably - the optimiser can make different choices depending on the statistics - so for some queries both versions may call the function only 10 times, or both call it 1 million times.

This hint should ensure that the second version is always fast, by telling Oracle not to merge the two queries into one:

select /*+ no_merge(v) */ col1, col2, myFun(col3)
  from (select col1, col2, col3
          from aTable
          join ...
          join ...
          where ...
       ) v;

Upvotes: 5

Related Questions