Oracle SQL: If I use a function both as a field and in order by, is it evaluated again?

Question

Suppose you have a PL/SQL function that returns some random value that you will use in a SQL sentence both as returned data and in the Order by clause.

Now you could write the Order by clause in three "different" ways:

By index

Select Foo,
       MyFunction(Foo) orderField
  From FooTable
 Order By 2

By calling "again" the function

Select Foo,
       MyFunction(Foo) orderField
  From FooTable
 Order By MyFunction(Foo)

By using the order field alias

Select Foo,
       MyFunction(Foo) orderField
  From FooTable
 Order By orderField

The third way is possible as the Order By clause is the last one of the select to be parsed and then Oracle already knows about the alias.

My question is, is there any difference in the processing or performance of these three queries? Particularly, would the second one imply that the MyFunction call would be evaluated again?

I tried to find out by searching over the docs, and by running some queries from Toad as well as having a look at the explain plan but could not find any significant difference until now.

My Oracle version is 11.2.0.3.0, if that matters in any way.

Answer 1

A good way to check what is going on in such cases is to use sequences (but I have oracle version 12.1). For example:

SQL> create sequence func_seq;

Sequence created.

SQL> create or replace function foo return number is
begin
  return func_seq.nextval;
end;
/

Function created.

First, make a query that return two rows (without ORDER BY clause) and check value of sequence:

SQL> select foo from dual connect by level <= 2;

       FOO
----------
         1
         2

SQL> select func_seq.currval from dual;

   CURRVAL
----------
         2

Then a query with ORDER BY:

SQL> select foo from dual connect by level <= 2 order by foo;

       FOO
----------
         3
         4

SQL> select func_seq.currval from dual;

   CURRVAL
----------
         4

In both cases function was executed 2 times.
But if your function takes arguments, you have to pay attention to their values:

SQL> create or replace function foo(p number) return number is
begin
  return func_seq.nextval;
end;
/  

Function created.

Make query with different arguments:

SQL> select foo(1) from dual connect by level <= 2 order by foo(2);

    FOO(1)
----------
         6
         8

SQL> select func_seq.currval from dual;

   CURRVAL
----------
         8

As we can see, function was executed 4 times.

Answer 2

The function is evaluated only once in your case, the expression in ORDER BY clause serves only as a reference to a column in the SELECT clause. When the expression doesn't match, the function will be evaluated twice, e.g.

Select Foo,
       MyFunction(Foo) orderField
From FooTable
Order By MyFunction(Foo)*2

I would expect Oracle caches the results of deterministic functions (this must be explicitely stated in the function definition), so in such cases the cached result is reused.