The Hash Semijoin variant of the Hash Join algorithm performs a semijoin between the left-hand side and the right-hand side. As with Nested Loops Semijoin described above, the right-hand side is only used to determine which rows from the left-hand side appear in the result. With Hash Semijoin the right-hand side is read to form an in-memory hash table which is subsequently probed by each row from the left-hand side. As soon as any match is found, the left-hand row is output to the result and the match process starts again for the next left-hand row. At least one equality join condition must be present for Hash Semijoin to be considered by the query optimizer. As with Nested Loops Semijoin, Hash Semijoin is utilized in cases where the join's inputs include table expressions from an existentially-quantified (IN, SOME, ANY, EXISTS) nested query that has been rewritten as a join. Hash Semijoin tends to outperform Nested Loops Semijoin when the join condition includes inequalities, or if a suitable index does not exist to make indexed retrieval of the right-hand side sufficiently inexpensive.
As with Hash Join, the Hash Semijoin algorithm may revert to a nested loops semijoin strategy if there is insufficient cache memory to enable the operation to complete. Should this occur, 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.
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.
The Windows Performance Monitor may not be available on Windows CE.
For more information, see QueryLowMemoryStrategy in Connection-level properties.