Executing UDRs in Parallel

The database server can execute the following UDRs in parallel if they are part of a PDQ and PDQPRIORITY is turned on:

• C UDRs that call only DataBlade API functions that are PDQ thread-safe can execute in parallel.
• Java UDRs that call only DataBlade API functions that are PDQ thread-safe can execute in parallel.

  For more information, refer to Writing PDQ Thread-Safe UDRs.

• Built-in function UDR

  Examples of built-in function UDRs include overloaded operators for UDTs, such as the following operators that are used for a generic B-tree index:

  • lessthan (<)
  • lessthanorequal (<=)
  • equal (=)
  • greaterthanorequal (>=)
  • greaterthan (>)

UDRs can execute in parallel in the following situations if they are part of a PDQ and PDQPRIORITY is turned on:

• A UDR used as an expression in a query
• DataBlade API FastPath executing a UDR
• Implicit UDR execution when evaluating a user-defined aggregate on a column of a user-defined type
• Implicit UDR execution for overloading of comparison operator
• Assign UDR executed implicitly
• Comparison UDR execution for sort
• A UDR that a generic B-tree index executes

A UDR cannot execute the following SQL statements in parallel:

• Singleton execution with the EXECUTE FUNCTION statement in either DB–Access or ESQL/C
• INSERT INTO tablename EXECUTE udr()
• FOREACH EXECUTE udr() END FOREACH
• OPEN CURSOR EXECUTE udr()
• Remote UDR execution

Execution of a UDR in a Query Expression

One way to execute UDRs is as 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 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
• Virtual Table Interface (VTI) or Virtual Index Interface (VII) fragments, provided that all am_purpose functions for the VTI or VII are all parallelizable

Parallel UDR in WHERE Clause

The following example is a typical PDQ that contains two UDRs:

SELECT c_udr1(tabid) FROM tab 
   WHERE tabname = c_udr2(3) AND 
      tabid > 100;

If the table tab has multiple fragments and the optimizer decides to run the select statement in parallel, the following operations can execute in parallel:

• The scan of table tab is performed by multiple scan threads in parallel. Each scan thread fetches a row from a fragment of tab.
• Each scan thread also evaluates the WHERE condition in parallel. Each scan thread executes the UDR c_udr2() in parallel.
• Each scan thread also executes the UDR c_udr1() in the select list in parallel.

Parallel UDR in a Join

The following sample query contains a join between table t1 and t2:

SELECT t1.x, t2.y
   FROM t1, t2
   where t1.x = t2.y and
      c_udr(t1.z, t2.z, 3) > 5 and
      c_udr1(t1.u) > 4 and 
      c_udr2(t2.u) < 5;

If the tables t1 and t2 have multiple fragments and the optimizer decides to run the select statement in parallel, the following operations can execute in parallel:

• The scan of table t1 is performed by multiple scan threads in parallel. Each scan thread fetches a row from a fragment of t1 and executes the UDR c_udr1() in parallel.
• The scan of table t2 is performed by multiple scan threads in parallel. Each scan thread fetches a row from a fragment of t2 and executes the UDR c_udr2() in parallel.
• The join of tables t1 and t2 is performed by multiple join threads in parallel. Each join thread fetches a row from a fragment of t2 and executes the UDR c_udr() in parallel.

Parallel UDR in the Select List

If you use a UDR in the select list and do not specify a WHERE clause, the UDR can execute in parallel if any of the following conditions are true:

• The GROUP BY clause is specified in the query.
• The ORDER BY clause is specified in the query.
• An aggregate such as MIN, MAX, AVG is specified in the query.
• The query is a parallel INSERT...SELECT statement.
• The query is a SELECT...INTO statement.

The next section shows a sample query with a UDR in the select list and no WHERE clause.

Parallel UDR with GROUP BY

The following sample query contains a GROUP BY clause. This sample query has a UDR in the select list and no WHERE clause.

SELECT c_udr1(tabid), COUNT(*)
   FROM t1 GROUP BY 1;

If the optimizer decides to run the select statement in parallel, the following operations can execute in parallel:

• The scan of table t1 is performed by multiple scan threads in parallel. The table t1 has multiple fragments. Each scan thread fetches a row from a fragment of t1.
• Multiple threads execute the UDR c_udr2() in parallel to process the GROUP BY clause. If table t1 is unfragmented, the GROUP BY operation can still execute in parallel even though the scan operation does not execute in parallel.

Parallel UDR in Select List for Parallel Insert

The following sample query is a parallel insert statement. Suppose you create an opaque data type circle, create a table cir_t that defines a column of type circle, create a UDR area, and then execute the following sample query:

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

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

• 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.

• The UDR area(circle) in the select list

  If the table cir_t has multiple fragments, multiple area UDRs can execute in parallel, one UDR on each fragment.

• The INSERT into cir_t_target

  If the table cir_t_target has multiple fragments, multiple INSERT statements can execute in parallel, one on each fragment.

FastPath Execution of a UDR in a DataBlade API (C)

A C UDR can use the following DataBlade API calls to invoke a UDR directly:

• mi_routine_get()
• mi_routine_exec()

DataBlade API FastPath execution of a UDR executes in parallel as long as the UDR is parallelizable and calls only DataBlade API functions that are PDQ thread safe.

Implicit UDR Execution of a User-Defined Aggregate

A user-defined aggregate (UDA) can execute in parallel as long as the UDR is parallelizable and calls only DataBlade API functions that are PDQ thread safe.

For example, suppose you create a UDA named uda and use it in the following SQL query:

select grp, uda(udt_col) FROM tab GROUP BY grp;

If the data type of column udt_col is a UDT whose aggregation requires a UDR call, the following operations can execute in parallel:

• Each group thread executes the aggregation UDR uda in parallel.
• If the GROUP BY column grp is a UDT column, the equal() UDR function on the UDT column executes in parallel by the scan thread for the hash repartitioning on the group by keys.
• If the table tab is fragmented, multiple scan threads can read the table in parallel.

Implicit UDR Execution of a Comparison Operator

When you create opaque data types, you can create overloaded routines for comparison operators such as equal (=) or greaterthanorequal (>=).

The following sample query selects rows using a filter on the UDT column:

SELECT * FROM tab WHERE udt_col = "xyz";

The database server converts the comparison operator = to call the equal UDR on the udt_col column. If the table tab is fragmented, the following operations can execute in parallel:

• Multiple scans of the table can execute in parallel, one scan on each fragment.
• Multiple = comparison operators can execute in parallel, one operator per fragment of table tab.

Implicit Execution of an Assign UDR

When you create opaque data types, you create the support function assign() to insert, update, or load the UDT data in the table.

The following sample SQL statement inserts data in a UDT column:

INSERT INTO tab (udtcol) SELECT udtcol FROM t1;

If the table tab has multiple fragments and the udtcol data type has an assign() function, each insert thread that inserts a fragment of table tab executes the assign() UDR in parallel.

The support function destroy() for a UDT does not execute in parallel because the destroy() UDR is called during a DELETE statement that is not executed in parallel.

Execution of a Comparison UDR for Sort

When you create opaque data types, you create the support function compare() to sort the UDT data during ORDER BY, UNIQUE, DISTINCT, and UNION clauses and CREATE INDEX operations.

SELECT udtcol FROM t ORDER BY 1;

If the udtcol column has a comparison UDR that is parallelizable and the client enables parallel sort, each sort thread participating in the parallel sort for the order by clause executes the comparison UDR in parallel.

Execution of a UDR by an Index on a UDT column

The database server supports indexing on a uDT column. Therefore, index build, search, and recovery require execution of UDRs that operate on UDT columns.

Currently, the database server does not support fragmentation by expression on UDT columns. As a result, the index built on a UDT column by the database server is not fragmented because index fragmentation makes sense only if the fragmentation is based on expression.