PIPELINED Vs Non-PIPELINED Function.

CREATE TYPE all_obj_type IS OBJECT
(
  OWNER              VARCHAR2(30),
  OBJECT_NAME        VARCHAR2(30),
  SUBOBJECT_NAME    VARCHAR2(30),
  OBJECT_ID          NUMBER,
  DATA_OBJECT_ID    NUMBER,
  OBJECT_TYPE        VARCHAR2(19),
  CREATED            DATE,
  LAST_DDL_TIME      DATE
); 
/

CREATE OR REPLACE TYPE all_obj_tt IS TABLE OF all_obj_type;
/

CREATE OR REPLACE FUNCTION non_pipe_fnc
RETURN all_obj_tt IS
    all_obj  all_obj_tt := all_obj_tt();
BEGIN
    FOR r IN (SELECT * FROM ALL_OBJECTS)
    LOOP
        all_obj.EXTEND;                   
        all_obj(all_obj.COUNT) := all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME);   
    END LOOP;
    RETURN all_obj;
END non_pipe_fnc;
/

CREATE OR REPLACE FUNCTION pipe_fnc
RETURN all_obj_tt
PIPELINED IS
BEGIN
    FOR r IN (SELECT * FROM ALL_OBJECTS)
    LOOP       
        PIPE ROW(all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME));
    END LOOP;
    RETURN;
END pipe_fnc;   
/




scott@10G> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
scott@10G> select * from TABLE(non_pipe_fnc());

41042 rows selected.

Elapsed: 00:00:03.64

Execution Plan
----------------------------------------------------------
Plan hash value: 1517095174

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |  8168 | 16336 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| NON_PIPE_FNC |       |       |            |          |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       6794  recursive calls
          0  db block gets
      87847  consistent gets
        101  physical reads
          0  redo size
    1587183  bytes sent via SQL*Net to client
       3399  bytes received via SQL*Net from client
        275  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
      41042  rows processed

scott@10G> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
scott@10G> select * from TABLE(pipe_fnc());

41042 rows selected.

Elapsed: 00:00:02.84

Execution Plan
----------------------------------------------------------
Plan hash value: 2394767287

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |  8168 | 16336 |    25   (4)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PIPE_FNC |       |       |            |          |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       5819  recursive calls
          0  db block gets
      86292  consistent gets
          0  physical reads
          0  redo size
    1587183  bytes sent via SQL*Net to client
       3399  bytes received via SQL*Net from client
        275  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
      41042  rows processed

Name                                       Run1             Run2  Diff
STAT...session uga memory      261,856          0        -261,856
STAT...physical read total byt   851,968          0        -851,968
STAT...physical read bytes       851,968           0        -851,968
STAT...session pga memory     3,080,192        0      -3,080,192
STAT...session uga memory max  16,811,116  0     -16,811,116
STAT...session pga memory max  36,569,088  0     -36,569,088     

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     266,350     266,319         -31    100.01%

PL/SQL procedure successfully completed.