{"id":495,"date":"2010-11-26T15:40:00","date_gmt":"2010-11-26T15:40:00","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=495"},"modified":"2010-11-26T10:35:00","modified_gmt":"2010-11-26T09:35:00","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=495","title":{"rendered":"Pipelined Table Functions"},"content":{"rendered":"

I have had trouble with a certain view I have to create at the customer site I am currently working at. The view involves 3 SQL queries combined by using a UNION ALL<\/a>, since the separate queries are mutually exclusive. Using the UNION ALL makes it a bit faster since Oracle<\/a> doesn’t have to do all the work of sorting and checking for duplicate rows. <\/p>\n

Retrieving data from this view resulted in memory errors since there is too much data for the server to come up with and to return to me. After checking all kinds of initialization parameters I decided to try and build a couple of pipelined table functions<\/a> that would retrieve the same data for me, but I assumed it wouldn’t put such a big pressure on my memory usage. <\/p>\n

<\/p>\n

A pipelined table function will come up with a single row for the result set and pipe it out of the function when it’s available. In my opinion there would only be need for enough memory for this single row instead of enough memory for all the rows of the entire result set. <\/p>\n

Since the function gets called from SQL the SQL engine needs to be aware of the result it produces. So I need to create types that define a row of the result. And since the function will return more than a single row, I also need to define a NESTED TABLE<\/a>, a collection of these rows. Using two types I can mimic the appearance of a table. <\/p>\n

Now all I have to do is create functions that will give me the result I am interested in. <\/p>\n

My \u2018normal\u2019 view looks like this:<\/p>\n

create or replace view v_emps as
\n
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
\n
   from emp e
\n
  where e.deptno = 10
\n
union all
\n
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
\n
  from emp e
\n
where e.deptno = 20
\n
union all
\n
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
\n
  from emp e
\n
where e.deptno = 30
\n
union all
\n
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
\n
  from emp e
\n
where e.deptno = 40
\n
\/<\/font> <\/p>\n

In the following example I create a new view based on the table functions for the EMP table. The SQL approach is better, easier to read and faster, but it shows what should be done if you want to move over to table functions.<\/p>\n

The way such a function look is kind of strange at first sight. <\/p>\n

Function f1 return mynestedtabletype pipelined
\n
Is
\n
  <declaration>
\n
  l_record myrecordtype;
\n
begin
\n
  loop
\n
    <do stuff to define l_record>
\n
    pipe row(l_record); — send the row out
\n
  end loop;
\n
  return; — return nothing but control
\n
end f1;
\n
<\/font><\/p>\n

First I create a type for the records I return from my function:<\/p>\n

CREATE TYPE emp_t AS OBJECT
\n
(
\n
  empno    NUMBER(4),
\n
  ename    VARCHAR2(10),
\n
  job      VARCHAR2(9),
\n
  mgr      NUMBER(4),
\n
  hiredate DATE,
\n
  sal      NUMBER(7, 2),
\n
  comm     NUMBER(7, 2)
\n
)
\n
\/<\/font><\/p>\n

Then I create a nested table of the record type I just created. The functions will return (or say they will return) a variable of this type:<\/p>\n

CREATE TYPE emps_tt AS TABLE OF emp_t
\n
\/<\/font><\/p>\n

Next is the package which will contain the table functions. I create 4 functions to retrieve all the datasets I need.<\/p>\n

CREATE OR REPLACE PACKAGE emp_tf IS 
  — Author  : Patrick Barel
\n
  — Purpose : Table Functions for EMP
\n
  — Public function and procedure declarations
\n
  FUNCTION get_accounting RETURN emps_tt PIPELINED;
\n
  FUNCTION get_research RETURN emps_tt PIPELINED;
\n
  FUNCTION get_sales RETURN emps_tt PIPELINED;
\n
  FUNCTION get_operations RETURN emps_tt PIPELINED;
\n
END emp_tf;
\n
\/<\/font><\/p>\n

CREATE OR REPLACE PACKAGE BODY emp_tf IS
\n
  — Function and procedure implementations
\n
  FUNCTION get_accounting RETURN emps_tt
\n
    PIPELINED IS
\n
    CURSOR c_emps IS
\n
      SELECT e.empno, e.ename, e.job, e.mgr
\n
            ,e.hiredate, e.sal, e.comm
\n
        FROM emp e
\n
       WHERE e.deptno = 10;
\n
    r_emps        c_emps%ROWTYPE;
\n
    l_returnvalue emp_t;
\n
  BEGIN
\n
    OPEN c_emps;
\n
    LOOP
\n
      FETCH c_emps INTO r_emps;
\n
      l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
\n
                            ,r_emps.mgr, r_emps.hiredate
\n
                            ,r_emps.sal, r_emps.comm);
\n
      EXIT WHEN c_emps%NOTFOUND;
\n
      PIPE ROW(l_returnvalue);
\n
    END LOOP;
\n
    CLOSE c_emps;
\n
    RETURN;
\n
  END get_accounting;
\n
  —
\n
  FUNCTION get_research RETURN emps_tt
\n
    PIPELINED IS
\n
    CURSOR c_emps IS
\n
      SELECT e.empno, e.ename, e.job, e.mgr
\n
            ,e.hiredate, e.sal, e.comm
\n
        FROM emp e
\n
       WHERE e.deptno = 20;
\n
    r_emps        c_emps%ROWTYPE;
\n
    l_returnvalue emp_t;
\n
  BEGIN
\n
    OPEN c_emps;
\n
    LOOP
\n
      FETCH c_emps
\n
        INTO r_emps;
\n
      l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
\n
                            ,r_emps.mgr, r_emps.hiredate
\n
                            ,r_emps.sal, r_emps.comm);
\n
      EXIT WHEN c_emps%NOTFOUND;
\n
      PIPE ROW(l_returnvalue);
\n
    END LOOP;
\n
    CLOSE c_emps;
\n
    RETURN;
\n
  END get_research;
\n
  —
\n
  FUNCTION get_sales RETURN emps_tt
\n
    PIPELINED IS
\n
    CURSOR c_emps IS
\n
      SELECT e.empno, e.ename, e.job, e.mgr
\n
            ,e.hiredate, e.sal, e.comm
\n
        FROM emp e
\n
       WHERE e.deptno = 30;
\n
    r_emps        c_emps%ROWTYPE;
\n
    l_returnvalue emp_t;
\n
  BEGIN
\n
    OPEN c_emps;
\n
    LOOP
\n
      FETCH c_emps
\n
        INTO r_emps;
\n
      l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
\n
                            ,r_emps.mgr, r_emps.hiredate
\n
                            ,r_emps.sal, r_emps.comm);
\n
      EXIT WHEN c_emps%NOTFOUND;
\n
      PIPE ROW(l_returnvalue);
\n
    END LOOP;
\n
    CLOSE c_emps;
\n
    RETURN;
\n
  END get_sales;
\n
  —
\n
  FUNCTION get_operations RETURN emps_tt
\n
    PIPELINED IS
\n
    CURSOR c_emps IS
\n
      SELECT e.empno, e.ename, e.job, e.mgr
\n
            ,e.hiredate, e.sal, e.comm
\n
        FROM emp e
\n
       WHERE e.deptno = 40;
\n
    r_emps        c_emps%ROWTYPE;
\n
    l_returnvalue emp_t;
\n
  BEGIN
\n
    OPEN c_emps;
\n
    LOOP
\n
      FETCH c_emps
\n
        INTO r_emps;
\n
      l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
\n
                            ,r_emps.mgr, r_emps.hiredate
\n
                            ,r_emps.sal, r_emps.comm);
\n
      EXIT WHEN c_emps%NOTFOUND;
\n
      PIPE ROW(l_returnvalue);
\n
    END LOOP;
\n
    CLOSE c_emps;
\n
    RETURN;
\n
  END get_operations;
\n
  —
\n
BEGIN
\n
  NULL;
\n
END emp_tf;
\n
\/<\/font><\/p>\n

In the implementation of the functions I could of course use BULK OPERATIONS to speed up the processing instead of the row-by-row (or slow-by-slow) approach I used now, but using bulk operations use more memory (the retrieved data is loaded into memory) and that is exactly what I am trying to avoid. Using BULK COLLECT with a limit clause might be an option, but that is something to explore later on.<\/p>\n

The new view looks like this:<\/p>\n

create or replace view v_emps_tf as
\n
select * from table(emp_tf.get_accounting)
\n
union all
\n
select * from table(emp_tf.get_research)
\n
union all
\n
select * from table(emp_tf.get_sales)
\n
union all
\n
select * from table(emp_tf.get_operations)
\n
\/<\/font><\/p>\n

Pretty much the same as the original one, but now based on my functions.<\/p>\n

Let\u2019s take a look at the \u2018strange\u2019 things here. When you look at the function specification it says it will return a nested table type, but in fact it will return nothing. Every result gets piped out of the function (PIPE ROW) when it is done.
\n
Another thing is the from part of the queries. We tell the SQL engine to treat the results of the function as if it were a table. For this we use the TABLE() operator.<\/p>\n

Doing some checks (select * from v_emp minus select * from v_emp_tf) tells me the result of both views is the same, since there are no rows returned. When I turn timing on I notice that the \u2018normal\u2019 SQL approach is faster than the table function approach. But the issue wasn\u2019t speed, it was memory usage.<\/p>\n","protected":false},"excerpt":{"rendered":"

I have had trouble with a certain view I have to create at the customer site I am currently working at. The view involves 3 SQL queries combined by using a UNION ALL, since the separate queries are mutually exclusive. Using the UNION ALL makes it a bit faster since Oracle doesn’t have to do […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-495","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=495"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/495\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}