|
The SQL Anywhere query optimizer chooses between two physical implementations of the set intersection SQL operator INTERSECT: a sort-based variant, Intersect Merge, and a hash-based variant, Intersect Hash.
Intersect Merge uses the Merge Join operator 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, Intersect Hash uses the Hash Inner Join algorithm to compute the set and bag intersection between the two inputs (INTERSECT and INTERSECT ALL).
If necessary, the Intersect 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 Intersect Hash algorithm is disabled on Windows CE in low memory situations.
In the case of INTERSECT, the Intersect Merge or Intersect Hash algorithm is coupled with one of the DISTINCT algorithms to ensure that the result does not contain duplicates. For the INTERSECT ALL query expression, the Intersect algorithm is coupled with a RowReplicate algorithm that computes the correct number of duplicate rows in the result.