The key to understanding parallel query processing in Adaptive Server is to understand the basic building blocks in a parallel query plan.
See Chapter 2, “Using showplan,” which explains how to display a
query plan in a text-based format for each SQL statement in a batch
or stored procedure.
A compiled query plan contains a tree of execution operators that closely resembles the relational semantics of the query. Each query operator implements a relational operation using a specific algorithm. For example, a query operator called the nested-loop join implements the relational join operation. In Adaptive Server, the primary operator for parallelism is the exchange operator, which is a control operator that does not implement any relational operation. An exchange operator is to create new worker processes that can handle a fragment of the data. During optimization, Adaptive Server strategically places the exchange operator to create operator tree fragments that can run in parallel. All operators found below the exchange operator (down to the next exchange operator) are executed by worker threads that clone the fragment of the operator tree to produce data in parallel. The exchange operator can then redistribute this data to the parent operator above it in the query plan. The exchange operator handles the pipelining and rerouting of data.
In the following sections, the word “degree” is used in two different contexts. When “degree N” of a table or index is referred to, it references the number of partitions contained in a table or index. When the “degree of an operation” or “the degree of a configuration parameter” is referred to, it references the number of partitions generated in the intermediate data stream.
The following example shows how operators in the query processor work in serial with the following query run in the pubs2 database. The table titles is hash-partitioned three ways on the column pub_id.
select * from titles QUERY PLAN FOR STATEMENT 1 (at line 1). 1 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCAN Operator | FROM TABLE | titles | Table Scan. | Forward Scan. | Positioning at start of table. | Using I/O Size 2 Kbytes for data pages. | With LRU Buffer Replacement Strategy for data pages.
As this example illustrates, the titles table is being scanned by the scan operator, the details of which appear in the showplan output. The emit operator reads the data from the scan operator and sends it to the client application. A given query can create an arbitrarily complex tree of such operators.
When parallelism turned on, Adaptive Server can perform a simple scan in parallel using the exchange operator above the scan operator. exchange produces three worker processes (based on the three partitions), each of which scans the three disjointed parts of the table and sends the output to the consumer process. The emit operator at the top of the tree does not know that the scans are done in parallel.
Example A:
select * from titles
Executed in parallel by coordinating process and 3 worker processes. 4 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |EXCHANGE Operator (Merged) |Executed in parallel by 3 Producer and 1 Consumer processes. | | |EXCHANGE:EMIT Operator | | | | |RESTRICT Operator | | | | | | |SCAN Operator | | | | FROM TABLE | | | | titles | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Executed in parallel with a 3-way partition scan. | | | | Using I/O Size 2 Kbytes for data pages. | | | | With LRU Buffer Replacement Strategy for data pages.
The operator called EXCHANGE: EMIT is
placed under an EXCHANGE operator to
funnel data. See “EXCHANGE operator”.
