Hello,

I post you my issue after Oracle support says there is no 'great' workaround.
It seems to be non documented and only oracle performance teams where informed of this 'expected' behavior (in their opinion).

To summarize the problem :

When you write a select like this :

SELECT MY_Column , My_RESULT , My_RESULT * 10 , My_RESULT * 20
from (
SELECT My_COLUMN , FUnction(parm ...) as My_result from my_table)
where My_result < 100

Oracle rewrites it like this depending on the execution plan :

SELECT MY_Column , FUnction(parm ...) , FUnction(parm ...) * 10 , FUnction(parm ...) * 20
from (
SELECT My_COLUMN , FUnction(parm ...) as My_result from my_table)
where FUnction(parm ...) < 100

So, as the Function is called more than once there are performance issues (if the function reads datas from tables) and consistency problems (if the function reads datas that change betwwen 2 calls).
Behavior is the same if SELECT My_COLUMN , FUnction(parm ...) as My_result from my_table is included in a view.

To demonstrate the issue, here is a small sample script with no tables needed, using a function that returns random values.

CREATE OR REPLACE FUNCTION function_test
RETURN NUMBER
AS
BEGIN
DBMS_OUTPUT.put_line (SYSTIMESTAMP || ' : into function ');

RETURN FLOOR (DBMS_RANDOM.VALUE (99, 0));
END;
/

SELECT my_result, my_result * 10, my_result * 20
FROM (SELECT function_test AS my_result FROM DUAL);

The result is :
MY_RESULT MY_RESULT*10 MY_RESULT*20
---------- ------------ ------------
45 970 520


What do you think about it ?

regards,

create or replace function function_test
  return number
as
begin
  dbms_output.put_line ( systimestamp || ' : into function ' ) ;
  return floor ( dbms_random.value ( 99, 0 ) ) ;
end;
/
select my_result, my_result * 10, my_result * 20
from  ( 
  select function_test as my_result from dual
) ; 

MY_RESULT  MY_RESULT*10  MY_RESULT*20  
83         570           700           

04-NOV-2016 08.20.19.162512000 : into function 
04-NOV-2016 08.20.19.162931000 : into function 
04-NOV-2016 08.20.19.163011000 : into function 
  
with rws as (
  select ( select function_test as my_result from dual ) my_result
  from dual
)
select my_result, my_result * 10, my_result * 20
from   rws; 

MY_RESULT  MY_RESULT*10  MY_RESULT*20  
42         420           840           

04-NOV-2016 08.20.19.678550000 : into function