Table function - pipleined vs non-pipelined

Env: Oracle 10g

1. Create a table called ORDERS  as follows:

   create table orders (
      order_number,
      create_date,
      assign_date,
      close_date,
      region_id )
   as select
  object_id,
  created,
  last_ddl_time,
  last_ddl_time +7,
  mod(object_id,8)
      from dba_objects
   /

scott@ORCL> select count(*) from orders;

  COUNT(*)
----------
     50377
  
2. Create a non-pipelined table function:

CREATE TYPE order_date_o AS object (
   order_number number,
   date_type char(1),   -- O, A, C
   year  number,
   quarter number,
   month  number
)
/

create type order_date_t as table of order_date_o
/


CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURS
                  RETURN order_date_t AS
   v_order_rec orders%ROWTYPE;
   v_ret_val order_date_t := order_date_t( );
BEGIN
   -- for every order in the cursor ...
   LOOP
      FETCH p_curs INTO v_order_rec;
      EXIT WHEN p_curs%NOTFOUND;
      -- extend the array by 3 and populate with component
      -- orders creation, assignment and close date
      v_ret_val.EXTEND(3);
      v_ret_val(v_ret_val.LAST-2) :=
         order_date_o(v_order_rec.order_number,
         'O',
         TO_CHAR(v_order_rec.create_date,'YYYY'),
         TO_CHAR(v_order_rec.create_date,'Q'),
         TO_CHAR(v_order_rec.create_date,'MM'));
      v_ret_val(v_ret_val.LAST - 1) :=
         order_date_o(v_order_rec.order_number,
         'A',
         TO_CHAR(v_order_rec.assign_date,'YYYY'),
         TO_CHAR(v_order_rec.assign_date,'Q'),
         TO_CHAR(v_order_rec.assign_date,'MM'));
      v_ret_val(v_ret_val.LAST) :=
         order_date_o(v_order_rec.order_number,
         'C',
         TO_CHAR(v_order_rec.close_date,'YYYY'),
         TO_CHAR(v_order_rec.close_date,'Q'),
         TO_CHAR(v_order_rec.close_date,'MM'));
   END LOOP;
   RETURN(v_ret_val);
END;
/


3. Create a pipelined table function

CREATE OR REPLACE FUNCTION date_parse_pl ( p_curs SYS_REFCURSOR )
                RETURN order_date_t
                PIPELINED AS

v_order_rec orders%ROWTYPE;

BEGIN
-- for every order in the cursor...
LOOP
  FETCH p_curs INTO v_order_rec;
  EXIT WHEN p_curs%NOTFOUND;

  -- pipe out the components of the orders open date
  PIPE ROW(order_date_o(v_order_rec.order_number,
                         'O',
                         TO_CHAR(v_order_rec.create_date,'YYYY'),
                         TO_CHAR(v_order_rec.create_date,'Q'),
                         TO_CHAR(v_order_rec.create_date,'MM')));

  -- pipe out the components of the orders assign date
  PIPE ROW(order_date_o(v_order_rec.order_number,
                        'A',
                        TO_CHAR(v_order_rec.assign_date,'YYYY'),
                        TO_CHAR(v_order_rec.assign_date,'Q'),
                        TO_CHAR(v_order_rec.assign_date,'MM')));

  -- pipe out the components of the orders close date
  PIPE ROW(order_date_o(v_order_rec.order_number,
                        'C',
                        TO_CHAR(v_order_rec.close_date,'YYYY'),
                        TO_CHAR(v_order_rec.close_date,'Q'),
                        TO_CHAR(v_order_rec.close_date,'MM')));

END LOOP;  -- every order in the cursor

RETURN;

END;
/


4. Comparing of the execution time for the following two statments in different session::

(a)
SELECT *
   FROM TABLE(date_parse(CURSOR(SELECT * FROM orders)))
WHERE ROWNUM <=10;   scott@ORCL> /

ORDER_NUMBER D       YEAR    QUARTER      MONTH
------------ - ---------- ---------- ----------
          20 O       2005          3          8
          20 A       2005          3          8
          20 C       2005          3          9
          44 O       2005          3          8
          44 A       2005          3          8
          44 C       2005          3          9
          28 O       2005          3          8
          28 A       2005          3          8
          28 C       2005          3          9
          15 O       2005          3          8

10 rows selected.

Elapsed: 00:00:07.51

(b)
/* test pipelined table function */
SELECT *
   FROM TABLE(date_parse_pl(CURSOR(SELECT * FROM orders)))
WHERE ROWNUM <=10;  scott@ORCL> @date_parse_pl.tst

ORDER_NUMBER D       YEAR    QUARTER      MONTH
------------ - ---------- ---------- ----------
          20 O       2005          3          8
          20 A       2005          3          8
          20 C       2005          3          9
          44 O       2005          3          8
          44 A       2005          3          8
          44 C       2005          3          9
          28 O       2005          3          8
          28 A       2005          3          8
          28 C       2005          3          9
          15 O       2005          3          8

10 rows selected.
Elapsed: 00:00:00.04


0.04 vs 7.51
~