Page 234 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 13: Parallel Databases

Figure 13.7 illustrates the parallel execution of our sample query. Notes
Figure 13.7: Inter-operation Parallelism and Dynamic Partitioning

As you can see from Figure 13.7, there are actually eight parallel execution servers involved in
the query even though the degree of parallelism is four. This is because a parent and child
operator can be performed at the same time (inter-operation parallelism).
Also note that 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 ename column between A and G, that row gets
sent to the first ORDER BY parallel execution server. When the scan operation is complete, the
sorting processes can return the sorted results to the coordinator, which in turn returns the
complete query results to the user.

Example: Parallel Query Intra- and Inter-operation
As an example of parallel query with intra- and inter-operation parallelism, consider a
more complex query:

SELECT /*+ PARALLEL(employees 4) PARALLEL(departments 4) USE_HASH(employees)
ORDERED */
MAX(salary), AVG(salary)
FROM employees, departments

WHERE employees.department_id = departments.department_id
GROUP BY employees.department_id;

Notes The query to force the join order and join method, and to specify the degree of
parallelism (DOP) of the tables employees and departments. In general, you should let the
optimizer determine the order and method.
The query plan or data flow graph corresponding to this query is illustrated in Figure 13.8.

LOVELY PROFESSIONAL UNIVERSITY 227