Order by Versus Cluster by

This alternative syntax is also allowed…

create or replace function My_Fn ( p_input_rows in My_Types.cur_t )
  return My_Types.items_tab
  pipelined
  order p_input_rows by (c1)

  parallel_enable
    ( partition p_input_rows by range (c1) )
is...

This means that those rows that are delivered to a particular slave as directed by partition... by will be locally sorted by that slave, thus parallelizing the sort. Therefore there should be no ORDER BY in the SELECT used to invoke the table function. (To have one would subvert the attempt to parallelize the sort.) Thus it’s natural to use the range option together with the order by option. This will be slower than cluster by, and so should be used only when the algorithm depends on it.

Note: the cluster... by construct cannot be used together with the order... by in the declaration of a table function. This means that an algorithm which depends on clustering on one key, c1, and then on ordering within the set row for a given value of c1 by, say, c2 would have to be parallelized by using the order... by in the declaration in the table function. (The algorithm shown here from the Runner's Training Logs example scenario presented in the code samples for Multilevel Collections has this character.) Here we would use…

create or replace function Median ( p_input_rows in My_Types.cur_t )
  return My_Types.items_tab
  pipelined
  order p_input_rows by (c1,c2)
  parallel_enable

    ( partition p_input_rows by range (c1) )
is...

The current restriction preventing using cluster... by together with order... by implies no loss of functionality, but only a missed opportunity to leverage the order N sort.

Caution: It is possible to design an algorithm for a table function which would deliver a different number of rows according to the degree of parallelism. The simplest example is a function which returns a table of NUMBER representing the count of the rows its input cursor delivered. A non-parallelized version would deliver just one row giving count(*) for the input table. A parallelized version would deliver N rows (where N is the degree of parallelism), the sum of whose values would give count(*) for the input table. However, this breaks the parallel query abstraction. Oracle recommends against programming this way.