Database Reference
In-Depth Information
The column that is of real interest is the Operation column that explains the various steps taken by the optimizer
to get the results sets together. Table
8-2
details out the description for the various operations.
Table 8-2.
Description of the XPLAN Operation Column
Id
Operation
Description
The statement being executed is a
SELECT
statement.
0
SELECT STATEMENT
1
PX COORDINATOR
This is the query coordinator (QC) process.
2
PX SEND QC (RANDOM)
The PX server process sends the data back to the QC process and the QC
process receives the data in a random order. Such an operation is normally
noticed when the query has no
ORDER BY
clause as part of the statement. In
the preceding example statement, we could also notice that the statement has
a
GROUP BY
clause but no
ORDER BY
clause.
3
HASH GROUP BY
An aggregation operation based on calculation of hash value on the columns
being grouped.
PX RECEIVE
4
The consumers receive the data collected by the producers, in this case
to group them before transferring the data to the coordinator. As noticed,
for every producer operation there is a consumer operation. The example
involves a four table join, and there are four sets of producers and consumers.
PX SEND HASH
5
Maps rows to individual PX servers using a hash function on the join key
access (
"N_REGIONKEY"="R_REGIONKEY"
). This method helps to achieve an
equal distribution of work for individual PX servers. The optimizer uses this
method of redistribution because of the
GROUP BY
clause in the statement.
HASH JOIN BUFFERED
This is a join operation between the
REGION
table and the
NATION
table using
the
REGIONKEY
column in the respective tables. These columns are identified
by
R_REGIONKEY
and
N_REGIONKEY
.
6
BUFFER SORT
7
The optimizer is using a buffering mechanism of a traditional sort operation;
however, there is no sort operation involved (the query does not contain an
ORDER BY
operator). In this specific case, the optimizer is buffering the data to
avoid multiple tabescans against real data blocks.
5
PX RECEIVE
8
The consumers receive the data collected by the producer. The consumers
receive the data collected by the producers in this case to group them before
transferring the data to the coordinator. As noticed, for every producer
operation there is a consumer operation. The example involves a four table
join, and there are four sets of producers and consumers, in this case to group
them before transferring the data to the coordinator. As noticed, for every
producer operation there is a consumer operation.
PX SEND BROADCAST
9
Due to a larger variation in the number of the rows of the tables involved in the
operation, the optimizer is using the broadcast method to redistribute data to
the parallel execution servers. Instead of redistributing rows from both result
sets, the database sends the smaller result set to all PX servers to guarantee the
individual servers are able to complete their join operation.
(
continued
)
Search WWH ::
Custom Search