|
|
|
Oracle hash joins
|
The most common
table join mechanisms are nested loop joins and
hash joins. In a nested loop join, the database
reads and index, builds a list of ROW ID’s and
then probes into the second table for the
matching rows.
In a hash join,
Oracle uses RAM memory to speed the join.
In a hash join,
the database does a full-scan of the driving
table, builds a RAM hash table, and then probes
for matching rows in the other table. For
certain types of SQL, the hash join will execute
faster than a nested loop join, but the hash
join uses more RAM resources. The propensity of
the SQL optimizer to invoke a hash join is
heavily controlled by the setting for the
hash_area_size
Oracle parameter. The larger the value for
hash_area_size,
the more hash joins the optimizer will invoke.
As we will soon learn in IT482, the
hash_area_size
defaults to double the value of the
sort_area_size parameter. Here is how to see
these values on your database:
SQL> show parameters area_size
NAME
TYPE VALUE
------------------------------------
----------- ----------------------
bitmap_merge_area_size
integer 1048576
create_bitmap_area_size
integer 8388608
hash_area_size
integer 1048576
sort_area_size
integer 524288
workarea_size_policy
string MANUAL
Pay special
attention to the readings on sort operations,
and understanding how Oracle first attempts to
sort row results sets inside the RAM memory of
the SGA or PGA. Sorting can occur whenever an
SQL statement contains an order by, or group by
clause. If there is no room in the RAM memory
region to sort the results set quickly, Oracle
will go to the temporary tablespace, and
complete the sort operation using disk storage.
The management of sorting is a very critical
part of SQL tuning because RAM memory sorts are
many thousands of times faster than sort that
have to be done inside the temporary table
space.
|
|
|
|
|
|
