Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Query Optimization and Execution » Query execution algorithms » Query expression algorithms

Query expression algorithms Next Page

Except algorithms

The SQL Anywhere query optimizer chooses between two physical implementations of the set difference SQL operator EXCEPT: a sort-based variant, Except Merge, and a hash-based variant, Except Hash.

Except Merge uses the Merge Join operator 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, Except Hash uses the Hash Anti-Semijoin algorithm to compute the set difference between the two inputs, and Hash Left-Outer join to compute the difference of the two inputs (EXCEPT ALL).

The Except Hash operator 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.

The Except Hash algorithm is disabled on Windows CE in low memory situations.

In the case of EXCEPT, the Except Merge or Except Hash algorithm is coupled with one of the DISTINCT algorithms to ensure that the result does not contain duplicates. For EXCEPT ALL, the Except algorithm is coupled with a RowReplicate algorithm that computes the correct number of duplicate rows in the result.

See also