Oracle SQL: if I use the function both as a field and in order, will it be evaluated again?

Question

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

Now you can write an Order by clause in three different ways:

By index

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

Calling the function again

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

Using the order field alias

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

The third way is possible, since the Order by clause is the last of what needs to be analyzed, and then Oracle already knows about this alias.

My question is, is there a difference in processing or executing these three queries? In particular, will there be a second call that the MyFunction call will be evaluated again?

I tried to find out by looking at the documents, and also ran some queries from Toad, and also looked at explain plan , but still could not find a significant difference.

My version of Oracle is 11.2.0.3.0, if that matters.

Answer 1

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

 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, create a query that returns two rows (without an ORDER BY ) and check the value of the sequence:

 SQL> select foo from dual connect by level <= 2; FOO ---------- 1 2 SQL> select func_seq.currval from dual; CURRVAL ---------- 2 

Then the 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, the function was performed 2 times.
But if your function accepts arguments, you should 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 a request 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 see, the function was performed 4 times.

Answer 2

The function is evaluated only once in your case, the expression in the ORDER BY expression serves only as a reference to the column in the SELECT . When the expression does not match, the function will be evaluated twice, for example.

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

I expect Oracle to cache the results of deterministic functions (this should be explicitly stated in the function definition), so in such cases, the cached result is reused.