In the previous article, I mentioned about pipelined table functions. In this article, I am going to talk about 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;
Pipelined table functions can be used in specific cases. It speeds up the query performance. In order to take advantage of the parallel feature of Oracle, make sure that your server supports multi-core cpu.