HashSemijoin performs a semijoin between the left-hand side and the right-hand side. The right-hand side is only used to determine which rows from the left-hand side appear in the result. With HashSemijoin, 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. If a match is found, the left-hand side row is output to the result and the match process starts again for the next left-hand side row. At least one equality join condition must be present for HashSemijoin to be considered by the query optimizer. As with NestedLoopsSemijoin, HashSemijoin is used when the join's inputs include table expressions from an existentially-quantified (IN, SOME, ANY, EXISTS) nested query that has been rewritten as a join. HashSemijoin tends to outperform NestedLoopsSemijoin 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 HashJoin, HashSemijoin may revert to a nested loops semijoin strategy if there is insufficient cache memory to allow 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.
The amount of memory that can be used by a HashSemijoin operator is dependent upon the multiprogramming level of the server, and the number of active connections. See Threading in SQL Anywhere, and Configuring the database server's multiprogramming level.
The Windows Performance Monitor may not be available on Windows Mobile.
For more information, see QueryLowMemoryStrategy in Connection properties.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|