Abstract plans for queries containing unions

The union abstract plan operator describes plans for SQL queries that contain unions:

select*
from
  t1,
  (select * from t2
  union
  select * from t3
 ) u(u1, u2)
where c11=u1
plan
“(nl_join
 (union
  (t_scan t2)
  (t_scan t3)
 )
 (i_scan i_c11 t1)
)”

There are two types of union in SQL: union distinct and union [all]. union [all] is the default.

The m_union_distinct and h_union_distinct abstract plan operators force the removal of merge or hash-based UNION DISTINCT duplicates. It is illegal to use these operators with a UNION ALL. The merge-based algorithm needs, from each of the union children, an ordering covering all union projection columns.

In the following example, the needed ordering is provided, for the first child, by the (c11, c12) composite index and, for the second child, by the sort.

select c11, c12 from t1
union distinct
select c21, c22 from t2
plan
“(m_union distinct
 (i_scan i_c11_c12 t1)
 (sort
  (t_scan t2)
 )
)”

The union_all and m_union_all abstract plan operators force the append- or merge-based UNION ALL. It is illegal to use these operators with a UNION DISTINCT. The merge algorithm needs no ordering for itself; it makes any useful ordering from the children available to the parent.

In the following example, the ordering provided by the two i_scan operators is made available, by their m_union_all parent, to the m_join above.

select *
from
  t1,
  (select c21, c22 from t2
   union
   select c31, c32 from t3
) u(u1, u2)
where c11=u1
plan
“(m_join
 (m_union_all
   (i_scan i_c21 t2)
   (i_scan i_c31 t3)
 )
 (i_scan i_c11 t1)
)”