The SQL Anywhere query optimizer chooses between two physical implementations of the set intersection SQL operator INTERSECT: a sort-based variant, MergeIntersect (IM), and a hash-based variant, HashIntersect (IH).

MergeIntersect uses MergeJoin to compute the set intersection between the two inputs through analyzing row matches in sorted order. In many cases an explicit sort of the two inputs is required. Similarly, HashIntersect uses HashJoin to compute the set and bag intersection between the two inputs (INTERSECT and INTERSECT ALL).

If necessary, HashIntersect may dynamically switch to a nested loops strategy if a memory shortage is detected. When this occurs, a performance counter is incremented. You can read this monitor with the QueryLowMemoryStrategy database or connection property, in the QueryLowMemoryStrategy statistic in the graphical plan (when run with statistics), or in the Query: Low Memory Strategies counter in the Windows Performance Monitor.

HashIntersect is disabled on Windows Mobile in low memory situations.

In the case of INTERSECT, MergeIntersect or HashIntersect is coupled with one of the DISTINCT algorithms to ensure that the result does not contain duplicates. For INTERSECT ALL operations, MergeIntersectAll and HashIntersectAll are coupled with RowReplicate, which computes the correct number of duplicate rows in the result.

See also

• INTERSECT clause
• Performing set operations on query results with UNION, INTERSECT, and EXCEPT
• QueryLowMemoryStrategy connection property: Connection-level properties
• QueryLowMemoryStrategy database property: Database-level properties
• Query: Low Memory Strategies statistic: Performance Monitor statistics