Pipelining

In reducing number of function calls I wrote about how to rewrite a query that is calling a function. Another approach to the issue is to alter the function. The function includes only a SQL clause. The whole result of the clause is bulk collected first and then returned. The usage of the function is in IN clause. IN predicate is satisfied if there is one equality coming out of the select. So in the best cases it is not needed to populate the whole bulk collect inside the function. Using pipelining as an alternative here.

Original bulk collect version.

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.

And the pipelined version of the function.

SQL> create or replace function rnpiped(n number) return ns_typ pipelined is
  2  begin
  3    for ret in
  4     (select level l from dual connect by level <= n)
  5    loop
  6     dbms_lock.sleep(1/n);
  7     pipe row (ret.l);
  8    end loop;
  9  end;
 10  /

If the whole result returned from the function is needed there is no great difference in the execution times. Actually it seems to be increasing a bit.

SQL> select * from table(rn(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.00
SQL>
SQL> select * from table(rnpiped(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.07

But when used in IN predicate the results with this data are even better than in the previous post.

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.00
SQL>
SQL> select * from ta a where a.n in (select * from table(rnpiped(a.m)));

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

Elapsed: 00:00:01.95

And putting both together.

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.09
SQL>
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(rnpiped(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:00.68

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;

select into

How come lines 9 and 10 do not execute in the following is_active function? Documentation says that use into_clause only when there is one row in the result. Should the remaining code be ignored when there are no rows. Be sure to have exactly that one row available when using select into structure. Count(*) is a way here to be sure there is the one row.

SQL> CREATE OR REPLACE function is_active(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select 1 into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      if ret is null then ret := 0;
11      end if;
12      return ret;
13  end;
14  /

Function created.

SQL>
SQL> select is_active(1),is_active(0) from dual
 2  ;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
          1

-0-before-1-
-1-after -1-
-0-before-0-
SQL>
SQL>
SQL> CREATE OR REPLACE function is_active_usingcount(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select count(*) into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      return ret;
11  end;
12  /

Function created.

SQL>
SQL> select is_active_usingcount(1),is_active_usingcount(0) from dual
 2  ;

IS_ACTIVE_USINGCOUNT(1) IS_ACTIVE_USINGCOUNT(0)
----------------------- -----------------------
                     1                       0

-0-before-1-
-1-after -1-
-0-before-0-
-0-after -0-

You get "ORA-01422: exact fetch returns more than requested number of rows" if there are more than one row. Should there be an exception also to the case when there are no rows?

Update 4.11.2010:
Well there is a exception thrown. Just not catching it anywhere.

CREATE OR REPLACE function is_active(i_is_active number)
       return number as
       ret number := 0;
  begin
       dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
       select 1 into ret
       from dual
      where i_is_active = 1;
     dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
     if ret is null then ret := 0;
     end if;
     return ret;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN return -1;
    end;
/

select is_active(1),is_active(0) from dual;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1            -1

PLSQL_WARNINGS

Struggling with pl/sql code having EXCEPTION WHEN OTHERS THEN NULL; lines.

alter session set plsql_warnings = 'ENABLE:6009';

and compile your code. You get a nice report of your buggy code. There are quite a few other warnings available since 10.1. Here is a sqlplus script to be run on your development/test environment to get a information about your possible problematic code. The compilewarnings.sql script generates a p.txt file containing the output.

SQL> compilewarnings.sql