Pipelined Table Functions

Table functions are used to return collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be complete before they are returned causing bottlenecks during large Extraction Transformation Load (ETL) operations. Pipelining negates the need to build huge collections by piping rows out of the function as they are created, allowing subsequent processing to start before the entire collection is fully populated. This article gives a simple example of a regular and a pipelined table function.

In order to create a table function we need to define a table type in the database. In this example we also define a row type as our table contains multiple columns:

DROP FUNCTION get_all_objects;
DROP TYPE object_table_type;
DROP TYPE object_row_type;

CREATE TYPE object_row_type AS OBJECT (
  OWNER              VARCHAR2(30),
  OBJECT_TYPE        VARCHAR2(18),
  OBJECT_NAME        VARCHAR2(30),
  STATUS             VARCHAR2(7)
);
/

CREATE TYPE object_table_type AS TABLE OF object_row_type;
/

Next we create a regular table function which returns a collection containing a subset of the columns from ALL_OBJECTS:

CREATE OR REPLACE FUNCTION get_all_objects (
  p_owner        IN  VARCHAR2 DEFAULT '%',
  p_object_type  IN  VARCHAR2 DEFAULT '%',
  p_object_name  IN  VARCHAR2 DEFAULT '%',
  p_status       IN  VARCHAR2 DEFAULT '%')
  RETURN object_table_type AS
  
  v_tab object_table_type := object_table_type();
BEGIN
  FOR cur IN (SELECT owner, object_type, object_name, status 
              FROM   all_objects
              WHERE  owner       LIKE p_owner
              AND    object_type LIKE p_object_type
              AND    object_name LIKE p_object_name
              AND    status      LIKE p_status
              ORDER BY owner, object_name, object_type)
  LOOP
    v_tab.extend;
    v_tab(v_tab.last) := object_row_type(cur.owner, cur.object_type, cur.object_name, cur.status);
  END LOOP;
  RETURN v_tab;
END;
/
SHOW ERRORS

If we query this table function with the following statement the collection is fully populated before any rows are returned giving a lag of 5 seconds before any rows are returned to my SQL*Plus session:

SELECT *
FROM   TABLE(get_all_objects) a;

If we now replace this function with the pipelined version below this time lag drops to approximately 2 seconds:

CREATE OR REPLACE FUNCTION get_all_objects (
  p_owner        IN  VARCHAR2 DEFAULT '%',
  p_object_type  IN  VARCHAR2 DEFAULT '%',
  p_object_name  IN  VARCHAR2 DEFAULT '%',
  p_status       IN  VARCHAR2 DEFAULT '%')
  RETURN object_table_type PIPELINED AS
BEGIN
  FOR cur IN (SELECT owner, object_type, object_name, status 
              FROM   all_objects
              WHERE  owner       LIKE p_owner
              AND    object_type LIKE p_object_type
              AND    object_name LIKE p_object_name
              AND    status      LIKE p_status
              ORDER BY owner, object_name, object_type)
  LOOP
    PIPE ROW(object_row_type(cur.owner, cur.object_type, cur.object_name, cur.status));        
  END LOOP;
  RETURN;
END;
/
SHOW ERRORS

Once you start working with large warehousing ETL operations the performance improvements can be massive, allowing data loads from external tables via table functions directly into the warehouse tables, rather than loading via a staging area.

For more information see:

Overview of Table Functions

Hope this helps. Regards Tim...

Back to the Top.