Database Reference
In-Depth Information
Figure 14-10.
On-disk sort, merge phase
6.
As soon as some data sorted in the right way is available, it can be returned to the parent
operation. This is step 6 in Figure
14-10
.
In the example just described, the data has been written and read into/from the temporary segment only once.
This type of sort is called a
one-pass sort
. When the size of the work area is much smaller than the amount of data to be
sorted, several merge phases are necessary. In such a situation, the data is written and read into/from the temporary
segment several times. This kind of sort is called a
multipass sort
. Obviously, from a performance point of view,
a one-pass sort should be faster than a multipass sort.
To recognize the two types of sorts, you can use the output generated by the
dbms_xplan
package. Let's take a look
at an output based on the four-table join already used in the previous section. In this output, two additional columns
are displayed:
1Mem
and
Used-Tmp
. The former is the estimated amount of memory needed for a one-pass sort. The
latter is the actual size of the temporary segment used by the operation during the execution. If no value is available,
it means that an in-memory sort has been performed. Also, note how the values between brackets are no longer 0 for
the operations using temporary space. Their value is set to the number of passes executed for the sort. In other words,
while operation 10 was a one-pass sort, operation 12 was a multipass (nine-pass) sort:
SELECT t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19
