One enormous step forward for PL/SQL in Oracle9i is the ability to now execute functions within a parallel query context. Prior to Oracle9i, a call to a PL/SQL function inside SQL caused serialization of that query -- a major problem for data warehousing applications.

With Oracle9i, you can now add information to the function header to instruct the runtime engine on how that function can and should be used.

Generally, if you would like your function to execute in parallel, then it must have a single strongly typed REF CURSOR input parameter. Here are some examples:

  1. Specify that the function can run in parallel and that the data passed to that function can be partitioned arbitrarily.

    CREATE OR REPLACE FUNCTION my_transform_fn ( 
         p_input_rows in employee_info.recur_t )
      RETURN employee_info.transformed_t
      PIPELINED
      PARALLEL_ENABLE ( PARTITION p_input_rows BY ANY )

  • Specify that the function can run in parallel and that all the rows for a given department go to the same slave, and that all these rows are delivered consecutively:

    CREATE OR REPLACE FUNCTION my_transform_fn ( 
         p_input_rows in employee_info.recur_t )
      RETURN employee_info.transformed_t
      PIPELINED
      CLUSTER P_INPUT_ROWS BY (department)
      PARALLEL_ENABLE
        ( PARTITION P_INPUT_ROWS BY HASH (department) )

  • Specify that the function can run in parallel and that the rows that are delivered to a particular slave process as directed by PARTITION... BY (for that specified partition) will be locally sorted by that slave, thus parallelizing the sort. As a result, one should not use an ORDER BY in the SELECT used to call that table function

    CREATE OR REPLACE FUNCTION my_transform_fn ( 
         p_input_rows in employee_info.recur_t )
      RETURN employee_info.transformed_t
      PIPELINED
      ORDER P_INPUT_ROWS BY (C1)
      PARALLEL_ENABLE
        ( PARTITION P_INPUT_ROWS BY RANGE (C1) )