How does Oracle process stored function calls in SQL?

guys. Say, I have a query:

select t.value, my_stored_function(t.value)
  from my_table t
 where my_stored_function(t.value) = n_Some_Required_Value

I have rewritten it in the following way:

select value, func_value
  from (select t.value, my_stored_function(t.value) func_value 
          from my_table t) subquery
 where subquery.func_value = n_Some_Required_Value

Let's think of my_stored_function as of resource-consuming one. I assume, in the second query it is called twice less, but I didn't experience any significant performance increase after this change.

So, I guess, my assumption was wrong. How does Oracle actually process these function calls then?

It's a really good question.

I first tried create table and insert sample data (five rows only):

create table my_table(value number);
insert into my_table(value) values(1);
insert into my_table(value) values(2);
insert into my_table(value) values(3);
insert into my_table(value) values(4);
insert into my_table(value) values(5);

I made a simple test package for testing this.

create or replace package my_package is
  g_counter_SELECT PLS_INTEGER := 0; -- counter for SELECT statement
  g_counter_WHERE  PLS_INTEGER := 0; -- counter for WHERE clause
  function my_function(number_in in number, type_in in varchar2) return number;
  procedure reset_counter;
end;
/

And body...

create or replace package body my_package is
  function my_function(number_in in number, type_in in varchar2) return number is
  begin
    IF(type_in = 'SELECT') THEN
        g_counter_SELECT := g_counter_SELECT + 1;
    ELSIF(type_in = 'WHERE') THEN
        g_counter_WHERE := g_counter_WHERE + 1;
    END IF;
    return mod(number_in, 2);
  end;
  procedure reset_counter is
  begin
    g_counter_SELECT := 0;
    g_counter_WHERE := 0;
  end;
end;
/

Now, we can run test on Oracle 9i (on 11g are same results):

-- reset counter
exec my_package.reset_counter();

-- run query
select t.value, my_package.my_function(t.value, 'SELECT')
  from my_table t
 where my_package.my_function(t.value, 'WHERE') = 1;

-- print result
exec dbms_output.put_line('Count (SELECT) = ' || my_package.g_counter_SELECT);
exec dbms_output.put_line('Count (WHERE) = ' || my_package.g_counter_WHERE);

Result is:

DBMS Output (Session: [1] SCOTT@ORA9i at: 08.09.2010 01:50:04): 
-----------------------------------------------------------------------
Count (SELECT) = 3
Count (WHERE) = 5

Here is plan table:

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | MY_TABLE    |       |       |       |
--------------------------------------------------------------------

Which means that the function (in WHERE calues) is called for every row of the table (in the case of FULL TABLE SCAN). In the SELECT statement is launched just as many times comply with condition WHERE my_function = 1

Now... test your second query (same results on Oracle9i and 11g)

Result is:

DBMS Output (Session: [1] SCOTT@ORA9i at: 08.09.2010 02:08:04): 
-----------------------------------------------------------------------
Count (SELECT) = 8
Count (WHERE) = 0

Explain plain look like this (for CHOOSE optimizer mode):

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | MY_TABLE    |       |       |       |
--------------------------------------------------------------------

QUESTION IS: Why Count (SELECT) = 8?

Because Oracle first run subquery (in my case with FULL TABLE SCAN, it's 5 rows = 5 calls my_function in SELECT statement):

select t.value, my_package.my_function(t.value, 'SELECT') func_value from my_table t

And than for this view (subquery is like view) run 3 times (due to the condition where subquery.func_value = 1) again call function my_function.

Personally not recommend to use function in the WHERE clause, but I admit that sometimes this is unavoidable.

As the worst possible example of this is illustrated by the following:

select t.value, my_package.my_function(t.value, 'SELECT')
  from my_table t
 where my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE');

Where the result on Oracle 9i is:

Count (SELECT) = 5
Count (WHERE) = 50

And on Oracle 11g is:

Count (SELECT) = 5
Count (WHERE) = 5

Which in this case shows that sometimes the use of functions may be critical for performance. In other cases (11g) it solves the database itself.

In both cases the function will be called once for every row in my_table. In the first case the call will be as a result of the where clause and the value it's just found will be returned without being calculated again. In the second case all the calculated values will be returned from the subquery and will then be filtered by the outer query's where clause.

Edit: Apparently not true based on Martin's testing. Now I have to go back and find the testing I did years ago that made me think this was the case, and see what I did wrong. The bit about FBIs is still true. I hope.

There may be some minor difference in memory usage and possibly the exact plan used by the optimizer but I wouldn't have thought either would be significant. Almost certainly not against the cost of the function call itself.

The only way I can see to optimize this is with a function based index.

A simple test:

create or replace function print_function(v1 number) return number is
begin
   dbms_output.put_line(v1);
   return v1;
end;
/

select print_function(ASCII(dummy)) as test
  from dual
 where chr(print_function(ASCII(dummy))) = dummy;

Results (using 10g):

      TEST
----------
        88

88
88

Conclusion: The function was executed separately in the SELECT and WHERE clauses.

You can use PL/SQL pragmas to affect the way oracle optimizes the query, see RESTRICT_REFERENCES Pragma