The SQL Anywhere query optimizer chooses between two physical implementations of the set difference SQL operator EXCEPT: a sort-based variant, MergeExcept (EM) and a hash-based variant, HashExcept (EH).

MergeExcept uses MergeJoin to compute the set difference between the two inputs through analyzing row matches in sorted order. In many cases an explicit sort of the two inputs is required. Similarly, HashExcept uses HashAntisemijoin to compute the set difference between the two inputs, and a left outer hash join to compute the difference of the two inputs (EXCEPT ALL).

HashExcept 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.

HashExcept is disabled on Windows Mobile in low memory situations.

In the case of EXCEPT, MergeExcept and HashExcept are coupled with one of the DISTINCT algorithms to ensure that the result does not contain duplicates. For EXCEPT ALL, HashExceptAll and MergeExceptAll are coupled with RowReplicate, which computes the correct number of duplicate rows in the result.

See also

• EXCEPT 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