Otech

magazine

partner

back

next

 

 

 

 

When would

you use a

normal table

function?

 

 

 

 

 

Dear Erik,

 

Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of data and which can be called in a normal SQL statement by using the TABLE() operator. Let’s create a simple function. Please note this is a demonstration only, not something you would normally solve using table functions (or PL/SQL as a whole).

First step is to create a record type in the database:

 

[PATRICK]SQL>CREATE TYPE emp_rt AS OBJECT

          2  ( empno NUMBER(4)

          3  , ename VARCHAR2(10)

          4  , mgr   NUMBER(4)

          5  )

          6  /

 

Then you need to create a table type in the database:

 

[PATRICK]SQL>CREATE TYPE emp_tt AS TABLE OF emp_rt

          2  /

 

Then it is time for the simple function. The DBMS_LOCK.SLEEP call is in there to show the difference between Table Functions and Pipelined Table Functions.

 

[PATRICK]SQL>CREATE OR REPLACE FUNCTION tf_emp RETURN emp_tt

          2  AS

          3    l_returnvalue emp_tt;

          4  BEGIN

          5    SELECT emp_rt(e.empno, e.ename, e.mgr)

          6      BULK COLLECT INTO l_returnvalue

          7      FROM emp e

          8     ORDER BY e.deptno;

          9    FOR indx IN l_returnvalue.first ..

         10                l_returnvalue.last LOOP

         11      l_returnvalue(indx).ename :=

         12        INITCAP(l_returnvalue(indx).ename);

         13      dbms_lock.sleep(.25); -- for demo purposes only

         14    END LOOP;

         15    RETURN l_returnvalue;

         16  END;

         17  /

 

Now you can call the function in the FROM clause of your SQL statement as if it were a relational table:

 

[PATRICK]SQL>SELECT *

          2    FROM TABLE(tf_emp)

          3  /

 

Notice that the result is displayed after all records have been processed, i.e. after 3.5 seconds (due to the DBMS_LOCK.SLEEP statement).

Now let’s create a PIPELINED table function, which produces the same result but in a different manner:

 

[PATRICK]SQL>CREATE OR REPLACE FUNCTION ptf_emp RETURN emp_tt

          2                    PIPELINED

          3  AS

          4    l_emps emp_tt;

          5    l_returnvalue emp_rt;

          6  BEGIN

          7    SELECT emp_rt(e.empno, e.ename, e.mgr)

          8      BULK COLLECT INTO l_emps

          9      FROM emp e

         10     ORDER BY e.deptno;

         11    FOR indx IN l_emps.first ..

         12                l_emps.last LOOP

         13      l_returnvalue :=

         14        emp_rt(empno => l_emps(indx).empno

         15              ,ename => INITCAP(l_emps(indx).ename)

         16              ,mgr => l_emps(indx).mgr);

         17      PIPE ROW (l_returnvalue);

         18      dbms_lock.sleep(.25); -- for demo purposes only

         19    END LOOP;

         20    RETURN;

         21  END;

         22  /

 

If you set the arraysize of your SQL*Plus session (or your command window in PL/SQL Developer) you can see how the results are being returned as they are produced, i.e. 0.25 seconds apart.

 

[PATRICK]SQL>SET ARRAYSIZE 1

[PATRICK]SQL>SELECT *

          2    FROM TABLE(ptf_emp)

          3  /

 

 

 advertisement

 

 

 

Now you can see the difference between a Table Function and a Pipelined Table Function. Pipelined Table Functions are best used when you are executing different stages of transformation of your data, for example reading from an OLTP system and writing to a DataWareHouse system. If you PARALLEL_ENABLE your functions AND your source table can be read parallel then you could really see some performance benefits.

But all of this doesn’t explain why you should NOT use pipelining in a table function. The ONLY reason I can think of is when you want to be able to call the function from plain PL/SQL. PL/SQL does one call to a function and expects one result from it. Not a result spread out over many ‘callback’s.

If you create a function like this:

 

[PATRICK]SQL>CREATE OR REPLACE PROCEDURE call_tf AS

          2    l_emps emp_tt;

          3  BEGIN

          4    l_emps := tf_emp;

          5    FOR indx IN l_emps.first .. l_emps.last LOOP

          6      dbms_output.put_line(l_emps(indx).empno ||

          7                          ' ' || l_emps(indx).ename ||

          8                          ' ' || l_emps(indx).mgr);

          9    END LOOP;

         10  END;

         11  /

 

And call this function, then everything works ok. It takes about 3.5 seconds for the function to complete, due to the DBMS_LOCK.SLEEP call in the tf_emp function.

If you call the pipelined table function you are stopped at compile time. The call to a pipelined table function is not allowed.

 

[PATRICK]SQL>CREATE OR REPLACE PROCEDURE call_ptf AS

          2    l_emps emp_tt;

          3  BEGIN

          4    l_emps := ptf_emp;

          5    FOR indx IN l_emps.first .. l_emps.last LOOP

          6      dbms_output.put_line(l_emps(indx).empno ||

          7                          ' ' || l_emps(indx).ename ||

          8                          ' ' || l_emps(indx).mgr);

          9    END LOOP;

         10  END;

         11  /

Warning: Procedure created with compilation errors.

[PATRICK]SQL>sho err

Errors for PROCEDURE CALL_PTF:

LINE/COL ERROR

-------- ---------------------------------------------------------------------

1/10     PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

 

You can of course wrap the call to the pipelined table function in a cursor like this:

 

[PATRICK]SQL>CREATE OR REPLACE PROCEDURE call_ptf2 AS

          2    CURSOR c_emps IS

          3      SELECT emp_rt(t.empno, t.ename, t.mgr)

          4        FROM TABLE(ptf_emp) t;

          5    l_emps emp_tt;

          6  BEGIN

          7    OPEN c_emps;

          8    FETCH c_emps BULK COLLECT

          9      INTO l_emps;

         10    FOR indx IN l_emps.first .. l_emps.last LOOP

         11      dbms_output.put_line(l_emps(indx).empno ||

         12                          ' ' || l_emps(indx).ename ||

         13                          ' ' || l_emps(indx).mgr);

         14    END LOOP;

         15  END;

         16  /

 

Procedure created.

 

But when you call this function you will see that it takes about 3.5 seconds to fetch all the records, effectively using the pipelined table function as a normal table function. This might be your escape to use pipelined table functions in a SQL only environment and still use the same function in a PL/SQL environment.

‘But you said pipelined table functions are best used then executing different stages of transformation. That includes multiple PL/SQL functions.’ you might wonder. That is correct. A pipelined table function may call another pipelined table function. It can use for instance a collection as its input like this

 

[PATRICK]SQL>CREATE OR REPLACE FUNCTION ptf_emp3

          2                             (emps_in IN emp_tt)

          3                      RETURN emp_tt PIPELINED

          4  AS

          5    l_emp emp_rt;

          6  BEGIN

          7    FOR indx IN emps_in.first .. emps_in.last LOOP

          8      l_emp := emps_in(indx);

          9      l_emp.ename := UPPER(l_emp.ename);

         10      PIPE ROW (l_emp);

         11      dbms_lock.sleep(.25); -- for demo purposes only

         12    END LOOP;

         13    RETURN;

         14  END;

         15  /

 

When you call this function using this:

 

[PATRICK]SQL>SET ARRAYSIZE 1

[PATRICK]SQL>SELECT *

          2    FROM TABLE(ptf_emp3(CAST(MULTISET (SELECT *

          3                                  FROM TABLE(ptf_emp))

          4                                          AS emp_tt)))

          5  /

 

You will notice the output will not start displaying until after about 3.5 seconds which tells me the call to ptf_emp must be completed before pft_emp3 can start doing its work.

If you change the parameter to accept a cursor (and of course change the processing as well) like this:

 

[PATRICK]SQL>CREATE OR REPLACE FUNCTION ptf_emp4

          2                    (cursor_in IN SYS_REFCURSOR)

          3                      RETURN emp_tt PIPELINED

          4  AS

          5    l_emp emp_rt;

          6  BEGIN

          7    LOOP

          8      FETCH cursor_in

          9        INTO l_emp;

         10      EXIT WHEN cursor_in%NOTFOUND;

         11      l_emp.ename := upper(l_emp.ename);

         12      PIPE ROW(l_emp);

         13      dbms_lock.sleep(.25); -- for demo purposes only

         14    END LOOP;

         15    RETURN;

         16  END;

         17  /

 

And call this function using this statement:

 

[PATRICK]SQL>SET arraysize 1

[PATRICK]SQL>SELECT *

          2    FROM TABLE(ptf_emp4(CURSOR (SELECT emp_rt(empno

          3                                             ,ename

          4                                             ,mgr)

          5                            FROM TABLE(ptf_emp))))

          6  /

 

You will notice the output starting to display after about half a second. The total time needed for this function to complete is roughly the same as the previous, but this function starts processing sooner, which might be exactly what you need.

 

I hope this sheds a bit of light on the subject. I think bottom line is to always use pipelined table functions instead of normal table functions, except when the function is only meant for PL/SQL or client consumption. You can always work around implementation restrictions if you need to use the same codebase in both SQL and PL/SQL.

 

Happy Oracle’ing,

Patrick Barel

 

 

If you have a question you want answered, please send an email to patrick@otechmag.com.

If I know the answer, or can find it for you, maybe I can help.

 

 

Would you like to ask Patrick a question? Click here and drop him a line.

Dear Patrick,

 

Last year I did a presentation on table functions at KScope. One of the questions I got was:

‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?

 

Erik van Roon

  • patrick barel   dear patrick, when would you use a normal table function?

OTECH MAGAZINE #7  spring 2015        copyright otech magazine 2015

www.otechmag.com