Parallel UDRs

One way to execute UDRs is in an expression in a query. You can take advantage of parallel execution if the UDR is in an expression in the query.

For parallel execution, the UDR must be in one of the following parts of a query:
  • WHERE clause
  • SELECT list
  • GROUP by list
  • Overloaded comparison operator
  • User-defined aggregate
  • HAVING clause
  • Select list for a parallel insertion statement
  • Generic B-tree index scan on multiple index fragments if the compare function used in the B-tree index scan is parallelizable
For example, suppose that you create an opaque data type circle, a table cir_t that defines a column of type circle, a user-defined routine area, and then run the following sample query: 
SELECT circle, area(circle) 
   FROM cir_t
   WHERE circle > "(6,2,4)";
In this sample query, the following operations can run in parallel:
  • The UDR area(circle) in the SELECT list

    If the table cir_t is fragmented, multiple area UDRs can run in parallel, one UDR on each fragment.

  • The expression circle > "(6,2,4)" in the WHERE clause

    If the table cir_t is fragmented, multiple scans of the table can run in parallel, one scan on each fragment. Then multiple " >" comparison operators can run in parallel, one operator per fragment.

By default, a UDR does not run in parallel. To enable parallel execution of UDRs, you must take the following actions:
  • Specify the PARALLELIZABLE modifier in the CREATE FUNCTION or ALTER FUNCTION statement.
  • Ensure that the UDR does not call functions that are not PDQ thread-safe.
  • Turn on PDQ priority.
  • Use the UDR in a parallel database query.