Neoview Query Guide (R2.2)

set). Although the steps that process the join might be quite complex, all joins work with two
sides only.
As joins are processed in the query execution plan, the join results flow from the join to the next
operator, which can be in the same or a different process. A single process can include many
joins, one supplying another with source rows. All joins can be processed in parallel by ESPs.
Parallel joins can be against matching or nonmatching partitions of the table. For more information
about processes and process boundaries, see “Process Architecture For a Query” (page 28).
Nested Joins
In a nested join, a row from the outer table is used to probe the inner table for a match of one or
more rows. A buffer of rows is normally read from the outer table, and each row in turn is used
to probe the inner table. One message is sent to the inner table for each outer row.
The compiler chooses nested join when relatively few probes into the inner table are expected
and when the inner table is large.
In general, the estimated cardinalities for each operator will show the expected rows resulting
from the operation. With nested joins (and index-access joins and insert-select operations), the
cardinalities are often shown as “1” on the SPLIT_TOP and PARTITION_ACCESS operators.
For these operators, the PROBES field in the COST_DETAILS panel of VQP show the expected
rows accessed, and the cardinality value for the plan step shows the expected number of rows
accessed per probe.
NESTED_JOIN Operator
The NESTED_JOIN operator describes a portion of an execution plan that involves a nested join.
This operator sends each outer row to the inner table, where it eventually goes to a scan operation.
Normally, the inner scan access is keyed, and the number of outer probes is small, resulting in
an efficient join. The actual join is done in the inner scan instead of the NESTED_JOIN operator.
Nested joins support range operations (>=, >, <, <=) in addition to equijoins.
The NESTED_JOIN has two child operators. The description field for this operator contains:
Data TypeToken DescriptionToken
integerA sequential number assigned to the fragment. 0 is always master
executor and 1 is reserved for the Explain plan. Numbers 2 to n will be
ESP or DAM fragments.
fragment_id
integerThe fragment_id for the parent fragment of the current fragment. Value
is (none) for master executor.
parent_frag
textmaster, ESP, or DAMfragment_type
textInner or natural joinjoin_type
textName of join method: nested or in-order nestedjoin_method
text1 for Type1 or 2 for Type2, depending on parallel join algorithmparallel_join_type
expr(text)Expression of the ON clause that has not been pushed down to the inner
scan, typically empty
join_predicate
expr(text)Expression of the WHERE clause that has not been pushed down to the
inner scan, typically empty
selection_predicates
NESTED_SEMI_JOIN Operator
The NESTED_SEMI_JOIN operator returns only one matched row from the inner table and
ignores duplicate matches. See “NESTED_JOIN Operator” (page 37).
Understanding Joins 37