oracle - Difference between Table Function and Pipelined Function?

Question

CREATE OR REPLACE TYPE ty_1 AS OBJECT ( fn VARCHAR2(100),
                                        sl NUMBER,
                                        hd DATE );
CREATE OR REPLACE TYPE ty_1_table AS TABLE OF ty_1;

CREATE OR REPLACE FUNCTION FN_RET_COL
  RETURN ty_1_table
AS
  c ty_1_table := TY_1_TABLE();
  BEGIN
    c.extend;
    C(1) := TY_1('A', 1, '10-JUN-2013');
    c.extend;
    C(2) := TY_1('B', 2, '11-JUN-2013');
    c.extend;
    C(3) := TY_1('C', 3, '12-JUN-2013');

    RETURN c;
  END;

CREATE OR REPLACE FUNCTION FN_RET_PIPE RETURN ty_1_table PIPELINED IS
  BEGIN
    PIPE ROW (TY_1('A', 1, '10-JUN-2013'));
    PIPE ROW (TY_1('B', 2, '11-JUN-2013'));
    PIPE ROW (TY_1('C', 3, '12-JUN-2013'));
  END;

SELECT * FROM TABLE (fn_ret_col);

SELECT * FROM TABLE (fn_ret_pipe);

First one FN_RET_COL is Regular table function and second one FN_RET_PIPE is Pipelined Function. I studied in a book like Regular table functions require collections to be fully populated before they are returned where as PIPELINED FUNCTION use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. saving memory and allowing subsequent processing to start before all the rows are generated. My doubt is : How PIPELINED Function saves memory? If I am not wrong, It is piping all the rows and storing them in a memory area and then printing all the rows in the console. Or is it like, it is directly printing row by row as soon as a new record is piped in the console without storing it anywhere?

CREATE OR REPLACE FUNCTION FN_RET_COL RETURN TY_1_TABLE
 PIPELINED IS
 BEGIN   
    PIPE ROW(TY_1('A',1,'10-JUN-2013'));
    DBMS_LOCK.sleep(seconds => 10);
    PIPE ROW(TY_1('B',2,'11-JUN-2013'));
    DBMS_LOCK.sleep(seconds => 10);
    PIPE ROW(TY_1('C',3,'12-JUN-2013'));
 END;

If my second case is right, then how does the above code works?

Answer 1

Pipelined functions, a very classic example is where you do a SELECT * FROM table name in SQL*Plus. What happens is, Oracle streams the data from the table..

Like watching a video in youtube.

Please note the word, 'Streaming'.. And in our function we define how many rows we stream.. Every streamed row is immediately available to the caller. Pipelining means in lay man terms, dont make me wait till you complete, give me what ever you have, and keep processing and updating me simultaneously.

In your last procedure, after piping every row, you initiate a sleep call for 10s , so the record is Streamed to the caller every 10s.

And, a normal table function will keep waiting until all processing work is done, and then it will return the reference to the result set cursor.

pipelined functions , they claim to save memory, is by flushing the content immediately, and hence the buffer being used is always minimal, whereas the round trips count get higher.