Database Reference
In-Depth Information
-----------------------------------------------------------------------------
| Id | Operation | Name | OMem | 1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MERGE JOIN | | | | | |
| 2 | SORT JOIN | | 34816 | 34816 |30720 (0)| 1024 |
| 3 | MERGE JOIN | | | | | |
| 4 | SORT JOIN | | 5120 | 5120 | 4096 (0)| |
| 5 | MERGE JOIN | | | | | |
| 6 | SORT JOIN | | 3072 | 3072 | 2048 (0)| |
|* 7 | TABLE ACCESS FULL| T1 | | | | |
|* 8 | SORT JOIN | | 9216 | 9216 |18432 (0)| 1024 |
| 9 | TABLE ACCESS FULL| T2 | | | | |
|* 10 | SORT JOIN | | 99K| 99K|32768 (1)| 1024 |
| 11 | TABLE ACCESS FULL | T3 | | | | |
|* 12 | SORT JOIN | | 954K| 532K|41984 (9)| 2048 |
| 13 | TABLE ACCESS FULL | T4 | | | | |
-----------------------------------------------------------------------------
7 - filter("T1"."N"=19)
8 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
10 - access("T2"."ID"="T3"."T2_ID")
filter("T2"."ID"="T3"."T2_ID")
12 - access("T3"."ID"="T4"."T3_ID")
filter("T3"."ID"="T4"."T3_ID")
■
Usually, the output of the
dbms_xplan
package displays values about the size of memory in bytes.
Unfortunately, as pointed out in Chapter 10, the values in the
Used-Tmp
column must be multiplied by 1,024 to be
converted to bytes. For example, in the previous output operations 10 and 12 used 1mB and 2mB of temporary space,
respectively.
Caution
Hash Joins
This section describes how hash joins work. A description of their general behavior and some examples of two-table
and four-table joins are given first, followed by a description of the work areas used during processing. Finally,
a particular optimization technique, index joins, is described. All examples are based on the
hash_join.sql
script.
Concept
The two sets of data processed by a hash join are called
build input
and
probe input
. The build input is the left input,
and the probe input is the right input. As illustrated in Figure
14-11
, using every row of the build input, a hash table in
memory (also using temporary space, if not enough memory is available) is built. Note that the hash key used for that
purpose is computed based on the columns used as the join condition. Once the hash table contains all data from the
build input, the processing of the probe input begins. Every row is probed against the hash table to find out whether it
