HashAntisemijoin performs an anti-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 HashAntisemijoin, the right-hand side is read to form an in-memory hash table that is subsequently probed by each row from the left-hand side. Each left-hand side row is output only if it fails to match any row from the right-hand side. HashAntisemijoin is used when the join's inputs include table expressions from a quantified (NOT IN, ALL, NOT EXISTS) nested query that can be rewritten as an antijoin. HashAntisemijoin tends to outperform the evaluation of the search condition referencing the quantified query if a suitable index does not exist to make indexed retrieval of the right-hand side sufficiently inexpensive.
As with HashJoin, HashAntisemijoin may revert to a nested loops 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 HashAntisemijoin 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|