ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Join algorithms
The hash join algorithm builds an in-memory hash table of the smaller of its two inputs, and then reads the larger input and probes the in-memory hash table to find matches, which are written to a work table. If the smaller input does not fit into memory, the hash join operator partitions both inputs into smaller work tables. These smaller work tables are processed recursively until the smaller input fits into memory.
The hash join algorithm has the best performance if the smaller input fits into memory, regardless of the size of the larger input. In general, the optimizer will choose hash join if one of the inputs is expected to be substantially smaller than the other.
If the hash join algorithm executes in an environment where there is not enough cache memory to hold all the rows that have a particular value of the join attributes, then it is not able to complete. In this case, the hash join method discards the interim results and an indexed-based nested loops join is used instead. All of the rows of the smaller table are read and used to probe the work table to find matches. This indexed-based strategy is significantly slower than other join methods, and the optimizer will avoid generating access plans using a hash join if it detects that a low memory situation may occur during query execution. When the nested loops strategy is needed due to low memory, a performance counter is incremented. You can read this monitor with the QueryLowMemoryStrategy database/connection property, or in the "Query: Low Memory Strategies" counter in Windows Performance Monitor.
Note: Windows Performance Monitor may not be available on Windows CE, 95, 98, or Me.
For more information, see QueryLowMemoryStrategy in Connection-level properties.
The hash join algorithm computes all of the rows of its result before returning the first row.
The hash join algorithm uses a work table, which provides insensitive semantics unless a value-sensitive cursor has been requested.
Hash join locks rows in its inputs before they are copied to memory.