force subquery resolution first

Question

I'm creating a query which uses 2 embedded server functions multiple times.

Problem: the functions search through a decently large table, and they take a long time to execute.

Goal: Use a subquery as if it were a table so that I can reference columns without running 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;

When I run my code general.column1 will reference the function in the statement of column1, not the data returned by it (which is ultimately what I'm after).

I'm fairly new to SQL, so any help is appreciated and if you need more info, I'll do my best to provide it.

Thanks!

Answer 1

I suggest you tu use the subquery factoring. The first subquery will be executed only once and then used through the rest of he 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

Answer 2

In general what you want to do is in this case is take advatage of scalar subquery caching.

i.e. put:

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;

delcaring the function as deterministic too helps if it is deterministic.

a 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 test a call to the function 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 row). now lets use 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've only shown that you have a literal and no input that is varying per row (if this is the case, the number of calls after using scalar caching should be 1).

if we 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 down to 55. in 11g we have result_cache which we can put in place of deterministic, which would reduce the calls on subsequant runs to 0 calls.