Force subquery first
I am creating a request that uses two built-in server-side functions multiple times.
Problem: The functions do a search on a pretty decent table and they take a long time.
Purpose: Use the subquery as if it were a table so that I can refer to the columns without having to run the function to generate the column more than once.
Example Pseudocode:
Select general.column1, general.column2, general.column1-general.column2
from (select package.function1('I take a long time') column1,
package.function2('I take even longer') column2,
normal_column
from bigtable) general;
code>
When I run my code general.column1 will refer to the function in the expression column1, not the data it returns (this is ultimately what I want).
I'm new to SQL so any help is appreciated and if you need more information I'll do my best to provide it.
Thank!
source to share
I suggest you use subquery factoring. The first subquery will be executed only once and then used through the remaining query.
WITH function_result AS
(SELECT package.function1('I take a long time') column1
, package.function2('I take even longer') column2
FROM dual)
SELECT function_result.column1
, function_result.column2
, function_result.column1 - function_result.column2
, bigtable.normal_column
FROM bigtable
source to share
In general, what you want to do in this case is to get support for scalar subquery caching.
i.e. saying:
Select general.column1, general.column2, general.column1-general.column2
from (select (select package.function1('I take a long time') from dual) column1,
(select package.function2('I take even longer') from dual) column2,
normal_column
from bigtable) general;
function deduction as deterministic
also helps if it is deterministic.
small example:
SQL> create or replace function testfunc(i varchar2)
2 return varchar2
3 is
4 begin
5 dbms_application_info.set_client_info(userenv('client_info')+1 );
6 return 'hi';
7 end;
8 /
Function created.
now lets you test a function call like you have:
SQL> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select *
2 from (select testfunc(owner) a
3 from all_objects);
57954 rows selected.
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
57954
the function was called 57954 times (once per line). now allows scalar caching:
SQL> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
SQL> select *
2 from (select (select testfunc(owner) from dual) a
3 from all_objects);
57954 rows selected.
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
178
178 calls instead of 57k! in your case, you only showed that you have a literal value and no input that changes for each line (if so, the number of calls after using scalar caching should be 1).
if you add deterministic:
SQL> create or replace function testfunc(i varchar2)
2 return varchar2 deterministic
3 is
4 begin
5 dbms_application_info.set_client_info(userenv('client_info')+1 );
6 return 'hi';
7 end;
8 /
Function created.
SQL> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
SQL> select *
2 from (select (select testfunc(owner) from dual) a
3 from all_objects);
57954 rows selected.
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
55
now up to 55. At 11g we have result_cache
which we can put in place of deterministic, which will reduce calls on subsequential runs by 0 calls.
source to share