Multiple Query Execution Plans
The query optimizer can generate multiple QEPs if the query includes any of the following objects:
• SQL subqueries (in, exists, all, any, and so on.)
• SQL UNION / INTERSECT / EXCEPT clause
• SQL GROUP BY clause
• Views that need to be materialized
As an example of multiple QEPs, consider the processing of a view on a union. The following statement creates the view:
CREATE VIEW view1 AS
SELECT DISTINCT col1 FROM arel
UNION
SELECT DISTINCT col2 FROM arel;
There are two selects, designated #1 and #2 in their respective QEPs below. Now consider the query optimizer action in evaluating the following query:
SELECT * FROM view1;
This generates three QEPs, which are shown in order in the example QEP diagrams that follow:
1. The first select in the union
2. The second select in the union
3. Main query—the merged result of the two unioned selects
QUERY PLAN of union view T0
Sort Unique
Pages 1 Tups 156
D1 C10
/
Proj-rest
Heap
Pages 1 Tups 156
D1 C0
/
aindex
Isam(col2)
Pages 2 Tups 156
QUERY PLAN of union subquery
Sort Unique
Pages 1 Tups 156
D4 C10
/
Proj-rest
Heap
Pages 1 Tups 156
D4 C0
/
arel
Hashed(NU)
Pages 70 Tups 156
QUERY PLAN of main query
Sort Unique
Pages 1 Tups 156
D19 C20
/
Proj-rest
Heap
Pages 1 Tups 156
D12 C11
/
T0
Heap
Pages 1 Tups 156