Operations that require the output of other operations are known as consumer operations. In Figure 8-1, the
SORT operation is the consumer of the
JOIN operation because
SORT requires the
Consumer operations can begin consuming rows as soon as the producer operations have produced rows. In Example 8-2, while the parallel execution servers are producing rows in the
SCAN of the
sales table, another set of parallel execution servers can begin to perform the
JOIN operation to consume the rows.
Each of the two operations performed concurrently is given its own set of parallel execution servers. Therefore, both query operations and the data flow tree itself have parallelism. The parallelism of an individual operation is called intra-operation parallelism and the parallelism between operations in a data flow tree is called inter-operation parallelism. Due to the producer-consumer nature of the Oracle Database operations, only two operations in a given tree must be performed simultaneously to minimize execution time. To illustrate intra- and inter-operation parallelism, consider the following statement:
SELECT * FROM employees ORDER BY last_name;
The execution plan implements a full scan of the
employees table. This operation is followed by a sorting of the retrieved rows, based on the value of the
last_name column. For the sake of this example, assume the
last_name column is not indexed. Also assume that the DOP for the query is set to
4, which means that four parallel execution servers can be active for any given operation.
Figure 8-2 illustrates the parallel execution of the example query.
Figure 8-2 Inter-operation Parallelism and Dynamic Partitioning
As illustrated in Figure 8-2, there are actually eight parallel execution servers involved in the query even though the DOP is
4. This is because a producer and consumer operator can be performed at the same time (inter-operation parallelism).
Also all of the parallel execution servers involved in the scan operation send rows to the appropriate parallel execution server performing the
SORT operation. If a row scanned by a parallel execution server contains a value for the
last_name column between
G, that row is sent to the first
BY parallel execution server. When the scan operation is complete, the sorting processes can return the sorted results to the query coordinator, which, in turn, returns the complete query results to the user.