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
Last modified date: 06/28/2024