Special Case: Function behavior is independent of the partitioning of the input data

Consider a function which processes each row from its input cursor individually. (Such a transformation which generates two or more output rows from each input row – generically referred to as piviotting - benefits particularly from a table function implementation.) The syntax to parallelize this is straightforward thus…

create or replace function Rowwise_Xform_Fn ( p_input_rows in sys_refcursor )
  return My_Types.xforms_tab
  pipelined
  parallel_enable ( partition p_input_rows by any )
is

  v_in_row My_Types.input_row;
  v_out_row My_Types.xform_row;
begin
  loop
    fetch p_input_rows into v_in_row;
    exit when p_input_rows%notfound;
    v_out_row.n := v_in_row.n*2; v_out_row.typ := 'a';

    pipe row ( v_out_row );
    v_out_row.n := v_in_row.n*3; v_out_row.typ := 'b';
    pipe row ( v_out_row );
  end loop;
  close p_input_rows;
  return;
end Rowwise_Xform_Fn;
/

See code sample for the complete working example. They keyword any expresses the programmer’s assertion that the results are independent of the order in which the function gets the input rows. When this keyword is used, the runtime system randomly partitions the data among the query slaves. This keyword is appropriate for use with functions that take in one row, manipulate its columns, and generate output row(s) based on the columns of this row only. (Of course if this assertion doesn’t hold, then the output will not be predictable.) This is the small exception referred to above: the input ref cursor need not be strongly typed to be partitioned by any.) The ability to exploit the parallel potential of a table function depends on whether the source can be parallelized.