Reducing the number of function calls

Using a slow function call in your query? Maybe you are calling it unnecessarily.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

SQL>
SQL> create table ta as select level n, mod(level,3)+1 m from dual connect by level <= 10;

Table created.

SQL> select * from ta;

         N          M
---------- ----------
         1          2
         2          3
         3          1
         4          2
         5          3
         6          1
         7          2
         8          3
         9          1
        10          2

10 rows selected.

SQL> set timi on

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.01

The query is calling rn function for each ten rows of ta table. Each call takes one second as the function is using dbms_lock. There are only three distinct values that the function is needed to be called.

SQL> with aa as (
  2  select *
  3    from ta a
  4  ), bb as (
  5  select distinct m
  6    from aa
  7  ), cc as (
  8  select /*+materialize*/ b.m, dd.column_value n
  9    from bb b, table(rn(b.m)) dd)
 10  select *
 11    from aa
 12   where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.03

Alternatively you might consider using result cache for the function.

SQL> create or replace function rn(n number) return ns_typ result_cache is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.04
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.01
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.00

Cleanup

SQL> drop table ta purge;
SQL> drop function rn;
SQL> drop type ns_typ;