Parallel Execution - Expert Oracle Database Architecture - page 671

If you look at the steps from 5 on down, this is the query ( SELECT ) component. The scan of BIG_TABLE and hash join to
USER_INFO was performed in parallel, and each of the subresults was loaded into a portion of the table (step 3, the LOAD AS
SELECT ). After each of the parallel execution servers finished its part of the join and load, it sent its results up to the query
coordinator. In this case, the results simply indicated “success” or “failure,” as the work had already been performed.
And that is all there is to it—parallel direct path loads made easy. The most important thing to consider with
these operations is how space is used (or not used). Of particular importance is a side effect called extent trimming .
Let's spend some time investigating that now.
Parallel DDL and Extent Trimming
Parallel DDL relies on direct path operations. That is, the data is not passed to the buffer cache to be written later;
rather, an operation such as a CREATE TABLE AS SELECT will create new extents and write directly to them, and the
data goes straight from the query to disk in those newly allocated extents. Each parallel execution server performing
its part of the CREATE TABLE AS SELECT will write to its own extent. The INSERT /*+ APPEND */ (a direct path insert)
writes “above” a segment's HWM, and each parallel execution server will again write to its own set of extents, never
sharing them with other parallel execution servers. Therefore, if you do a parallel CREATE TABLE AS SELECT and use
four parallel execution servers to create the table, then you will have at least four extents—maybe more. But each of
the parallel execution servers will allocate its own extent, write to it and, when it fills up, allocate another new extent.
The parallel execution servers will never use an extent allocated by some other parallel execution server.
Figure 14-3 depicts this process. We have a CREATE TABLE NEW_TABLE AS SELECT being executed by four parallel
execution servers. In the figure, each parallel execution server is represented by a different color (white, light-gray,
dark-gray, or black). The boxes in the disk drum represent the extents that were created in some data file by this
CREATE TABLE statement. Each extent is presented in one of the aforementioned four colors, for the simple reason that all
of the data in any given extent was loaded by only one of the four parallel execution servers— p003 is depicted as having
created and then loaded four of these extents. p000 , on the other hand, is depicted as having five extents, and so on.
Figure 14-3. Parallel DDL extent allocation depiction