Parallel Processing for SPL Routines

In certain circumstances, the database server can use parallel execution for SQL statements in an SPL routine.

SQL Statements That Contain a Call to an SPL Routine

If an SQL statement contains a call to an SPL routine, the parts of the statement that are related to the SPL routine call are executed only by the connection coserver. SQL statements that are contained entirely in SPL routines might be executed in parallel, however.

The following sample query contains several calls to SPL routines:

SELECT y,x proc0(y) 
   FROM tab1, tab2 
      WHERE proc1(x) = proc2(y) AND x = proc3(x);

In this query, SELECT y, x and FROM tab1, tab2 might be parallelized, but the WHERE clause is executed only by the connection coserver. This restriction does not apply to execution of SQL statements that are contained in the SPL routines themselves, proc1(), proc2(), and proc3().

Figure 34 shows what the query plan might look like for this sample query. If the tables tab1 and tab2 are fragmented across multiple coservers, the optimizer can create multiple instances of the scan and join SQL operators to enable parallel execution.

Figure 34. Sample Query Plan for SELECT Statement That Calls Stored Procedures

The SQL operator EXPRESSION executes procedures that appear as part of expressions in the WHERE clause. The SQL operator PROJECT evaluates proc0(y), the procedure call in the select list.

SQL Statements in an SPL Routine

The optimizer might execute the SQL statements that are contained in an SPL routine in parallel. The degree of parallelism depends on factors such as the SQL operators that make up the query plan for the individual SQL statement, the fragmentation strategy of the tables that are involved, the number of CPU virtual processors available, and memory requirements. The query plans for the SPL routine SQL statements are cached so that they can be reused if the procedure is called again in the same session.