Given two sets of parallel execution servers SS1 and SS2 for the query plan illustrated in Figure 8-1, the execution proceeds as follows: each server set (SS1 and SS2) has four execution processes because of the
PARALLEL hint in the query that specifies the DOP.
Child set SS1 first scans the table
customers and sends rows to SS2, which builds a hash table on the rows. In other words, the consumers in SS2 and the producers in SS1 work concurrently: one in scanning
customers in parallel, the other is consuming rows and building the hash table to enable the hash join in parallel. This is an example of inter-operation parallelism.
After SS1 has finished scanning the entire
customers table, it scans the
sales table in parallel. It sends its rows to servers in SS2, which then perform the probes to finish the hash-join in parallel. After SS1 has scanned the
sales table in parallel and sent the rows to SS2, it switches to performing the
BY operation in parallel. This is how two server sets run concurrently to achieve inter-operation parallelism across various operators in the query tree.
Another important aspect of parallel execution is the redistribution of rows when they are sent from servers in one server set to servers in another. For the query plan in Example 8-2, after a server process in SS1 scans a row from the
customers table, which server process in SS2 should it send it to? The operator into which the rows are flowing decides the redistribution. In this case, the redistribution of rows flowing up from SS1 performing the parallel scan of
customers into SS2 performing the parallel hash-join is done by hash partitioning on the join column. That is, a server process scanning
customers computes a hash function of the value of the column
customers.cust_id to decide the number of the server process in SS2 to send it to. The redistribution method used in parallel queries explicitly shows in the
Distrib column in the
PLAN of the query. In Figure 8-1, this can be seen on lines 5, 8, and 12 of the