Database Reference
In-Depth Information
Looking at an execution plan can't tell you whether the database engine actually uses table prefetching or
batching. The fact is, even though the query optimizer produces an execution plan that can take advantage of either
table prefetch or batching, it's the execution engine that decides whether using that plan is sensible. The only way
to know whether an optimization technique is used is to look at the physical reads performed by the server
process—specifically, the wait events associated with them:
db file sequential read
event is associated with single-block physical reads.
Therefore, if it occurs, either no optimization technique took place or one was not needed
(for example, because the required blocks are already in the buffer cache).
•
The
db file scattered read
and
db file parallel read
events are associated with
multiblock physical reads. The difference between the two is that the former is used for
physical reads of adjacent blocks, and the latter is used for physical reads of nonadjacent
blocks. Therefore, if one of them occurs for rowid accesses or index range scans, it means that
an optimization technique took place.
•
The
Merge Joins
The following sections describe how
merge joins
(also known as
sort-merge joins
) work. I begin by describing their
general behavior and give some examples of two-table and four-table joins. Finally, I describe the work areas used
during processing. All examples are based on the
merge_join.sql
script.
Concept
The database engine, depending on the SQL statement and the physical database design, has the choice between
several ways to carry out merge joins. In the general case, both sets of data are read and sorted according to the
columns of the join condition. Once these operations are completed, the data contained in the two work areas is
merged, as illustrated in Figure
14-7
.
Merge joins are characterized by the following properties:
•
The left input is executed only once.
•
The right input is executed at most once. In the event the left input doesn't return any row,
the right input isn't executed at all.
•
Except when a Cartesian product is executed, the data returned by both inputs must be sorted
according to the columns of the join condition.
•
When data is sorted, both inputs must be fully read and sorted before returning the first row of
the result set.
•
All types of joins are supported.
Merge joins aren't used very often. The reason is that in most situations either nested loops joins or hash joins
perform better than merge joins.
