12. Using the Query Optimizer : Multiple Query Execution Plans
 
Share this page                  
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.