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 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
In the QEP pane of the SQL Scratchpad window in VDBA, these QEPs are shown in separate tabs.