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 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 provided that the compare function used in the B-tree index scan is parallelizable

For example, suppose 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 execute the following sample query:

SELECT circle, area(circle) 
   FROM cir_t
   WHERE circle > "(6,2,4)";

In this sample query, the following operations can execute in parallel:

• The UDR area(circle) in the SELECT list

  If the table cir_t is fragmented, multiple area UDRs can execute 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 execute in parallel, one scan on each fragment. Then multiple ">" comparison operators can execute in parallel, one operator per fragment.

By default, a UDR does not execute 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.