||Oracle Tips by Burleson
The hash-join has nothing to do with hash clusters or TABLE ACCESS
HASH method. A hash-join compares two tables in memory. The first
table is full table scanned, and a hashing function is applied to
the data in memory. Then the second table is full table scanned and
the hashing function is used to compare the values. Matching values
are returned to the user. The user usually has nothing to do with
this process; it is completely optimizer-controlled. However, it can
be used only by the cost-based optimizer. Generally, hash-joins will
gain something for you only if you are using parallel query.
Typically, the optimizer will use hash-joins for small tables that
can be scanned quickly. To use hash-joins, the HASH_JOIN_ENABLED
initialization parameter must be set to TRUE.
Several HASH parameters affect how hash-joins are used. These are:
HASH_JOIN_ENABLED. Set to TRUE to use hash-joins
HASH_AREA_SIZE. Large value reduces cost of hash-joins, so they are
used more frequently (set to half the square root of the size of the
smaller of the two objects, but not less than 1 megabyte). Suggested
range is between 8 and 32 megabytes. Defaults to twice
HASH_MULTIBLOCK_IO_COUNT. Large value reduces cost of hash-joins, so
they are used more frequently. Suggested size is 4.
This is an excerpt by
Mike Ault’s book “Oracle
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download.
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.