Thanks for the question, Philippe.
Asked: November 04, 2016 - 2:17 pm UTC
Last updated: November 09, 2016 - 1:45 am UTC
Version: 11GR2 / 12C
Viewed 1000+ times
You Asked
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,
with LiveSQL Test Case:
and Chris said...
Thanks for providing a link to LiveSQL Phillipe. Unfortunately I'm getting a "404 not found" error using it...
Yes, this is expected behaviour. But there is a way around this issue:
Scalar subquery caching!
Tom discusses this at:
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html Here's a quick example showing that Oracle only executes the function once when you use this:
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
There are some limits to this caching. Read Tom's article for the full details.
The consistency issue is a bigger problem. If your function executes SQL statements then you can get different results as you say:
https://blogs.oracle.com/sql/entry/the_problem_with_sql_calling So the general recommendation is:
Don't!
If for some reason you must use functions then you could add scn to the parameters. Then change the SQL inside the procedure to use "as of scn :scn" flashback queries.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment