| |
 |
|
Oracle Tips by Burleson |
Hash-Joins
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 SORT_AREA_SIZE.
-
HASH_MULTIBLOCK_IO_COUNT. Large value
reduces cost of hash-joins, so they are used more frequently.
Suggested size is 4.
See Code Depot
www.oracle-script.com |
