Paralel Enabled Pipelined Table Functions

In the previous article, I mentioned about pipelined table functions. I am going to explain parallel feature of pipelined table functions. At least one REF CURSOR use as function parameter. The basic syntax is shown below.

CREATE FUNCTION f(p <ref cursor type>) RETURN rec_tab_type PIPELINED
  PARALLEL_ENABLE(PARTITION p BY [{HASH | RANGE} (<column list>) | ANY ]) IS
BEGIN ... END;

Let’s start by creating a test table for our example and populate it.

CREATE TABLE T_PARALLEL_TEST
(
OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(150), 
OBJECT_TYPE VARCHAR2(50)
);

INSERT /*+ APPEND */ INTO T_PARALLEL_TEST
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

COMMIT;

In this example, I am going to use ANY partitioning type which randomly partitions the workload. As it is shown with the highlighted rows.

CREATE OR REPLACE PACKAGE PKG_TEST
IS
    TYPE REC_TEST IS RECORD(
        OBJECT_ID NUMBER,
        OBJECT_NAME VARCHAR2(150),
        OBJECT_TYPE VARCHAR2(50),
        SESSION_ID NUMBER
    );
     
    TYPE TYP_ARR IS TABLE OF REC_TEST;
    
    TYPE REF_CUR IS REF CURSOR RETURN T_PARALLEL_TEST%ROWTYPE;
     
    FUNCTION PARALLEL_TAB_FNC(P_CURSOR IN REF_CUR) RETURN TYP_ARR PIPELINED 
    PARALLEL_ENABLE(PARTITION P_CURSOR BY ANY); 
     
END; 
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
   
    FUNCTION PARALLEL_TAB_FNC(P_CURSOR IN REF_CUR) 
    RETURN TYP_ARR PIPELINED
    PARALLEL_ENABLE(PARTITION P_CURSOR BY ANY)
    IS
        V_REC REC_TEST;
    BEGIN
        
        LOOP
            FETCH P_CURSOR INTO V_REC.OBJECT_ID, V_REC.OBJECT_NAME, V_REC.OBJECT_TYPE;
            V_REC.SESSION_ID := SYS_CONTEXT('USERENV','SID');
            EXIT WHEN P_CURSOR%NOTFOUND;
            PIPE ROW(V_REC);
        END LOOP
        
        RETURN;
    END;
       
END;

In order to use parallel feature, PARALLEL hint is used in the query.

SELECT 
    OBJECT_TYPE, COUNT(*), SESSION_ID
FROM TABLE(
        PKG_TEST.PARALLEL_TAB_FNC(
                 CURSOR(SELECT /*+ PARALLEL(16) */  * FROM T_PARALLEL_TEST))
           )
GROUP BY OBJECT_TYPE, SESSION_ID;
Leave a Reply

Your email address will not be published.