|
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 chooses 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 avoids generating access plans using a hash join if it detects that a low memory situation may occur during query execution.
Memory governor limits are dependent upon the server's multiprogramming level and the number of active connections. See Threading in SQL Anywhere, and Setting the database server's multiprogramming level.
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 or connection property, or in the Query: Low Memory Strategies counter in the Windows Performance Monitor.
The Hash Join algorithm is disabled on Windows CE in low memory conditions.
NoteThe Windows Performance Monitor may not be available on Windows CE. |
For more information, see QueryLowMemoryStrategy in Connection-level properties, and Setting the database server's multiprogramming level.
The Hash Join algorithm also:
computes all of the rows in its result before returning the first row
uses a work table, which provides insensitive semantics unless a value-sensitive cursor has been requested
can be executed in parallel
locks rows in its inputs before they are copied to memory