Performance Analyzes Of PIPELINED Functions In Oracle

Oracle introduced PIPELINED TABLE functions with release 9. With the power of this kind of functions you can work without completing the process. When you process a row you pipe it. When you use pipelined functions you gain performance.

When declaring pipelined functions you must return an oracle collection with out parameter type. Although you sign the function with collection return type, you did not return any thing. Only pipe a row of collection. You can select the result of a pipelined function inside an sql statement.

I demonstrate a simple example to show this.


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop table dup;

Table dropped

Executed in 0,141 seconds

SQL> create table dup(i number, x varchar2(8), d date );

Table created

Executed in 0 seconds

SQL> BEGIN
  2    FOR i IN 1 .. 100000 LOOP
  3      INSERT INTO dup
  4      VALUES
  5        (trunc(dbms_random.VALUE(1, 100)),
  6         dbms_random.STRING('U', 5),
  7         trunc(SYSDATE + trunc(dbms_random.VALUE(1, 100))));
  8    END LOOP;
  9  END;
 10  /

PL/SQL procedure successfully completed

Executed in 18,281 seconds

SQL> drop type dup_tab;

Type dropped

Executed in 0,015 seconds

SQL> drop type dup_rec;

Type dropped

Executed in 0,063 seconds

SQL> create type dup_rec is object(i number, x varchar2(8), d date);
  2  /

Type created

Executed in 0,125 seconds

SQL> create type dup_tab is table of dup_rec;
  2  /

Type created

Executed in 0,031 seconds

SQL> CREATE OR REPLACE FUNCTION get_dup_pip RETURN dup_tab
  2    PIPELINED IS
  3  BEGIN
  4    FOR rec IN (SELECT i, x, d FROM dup) LOOP
  5      PIPE ROW(dup_rec(rec.i, rec.x, rec.d));
  6    END LOOP;
  7    RETURN;
  8  END get_dup_pip;
  9  /

Function created

Executed in 0,032 seconds

SQL> CREATE OR REPLACE FUNCTION get_dup_nopip RETURN dup_tab IS
  2    duplicates dup_tab := dup_tab();
  3    i          NUMBER := 1;
  4  BEGIN
  5    FOR rec IN (SELECT i, x, d FROM dup) LOOP
  6      duplicates.EXTEND;
  7      duplicates(i) := dup_rec(rec.i, rec.x, rec.d);
  8      i := i + 1;
  9    END LOOP;
 10    RETURN duplicates;
 11  END get_dup_nopip;
 12  /

Function created

Executed in 0,015 seconds

SQL> select count(*) from table( get_dup_pip );

  COUNT(*)
----------
    100000

Executed in 0,218 seconds

SQL> select count(*) from table( get_dup_nopip );

  COUNT(*)
----------
    100000

Executed in 0,75 seconds


SQL>




For more information please visit:
http://www.akadia.com/services/ora_pipe_functions.html
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:19481671347143
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2335