To adjust performance for queries that reference UNION ALL views, set the JOIN_PREFERENCE option, which affects joins between
UNION ALL views.
All partitions in a UNION ALL view must have a
complete set of indexes defined for optimization to work.
Queries with DISTINCT will tend
to run more slowly using a UNION
ALL view than a base table.
SAP Sybase IQ includes optimizations for UNION ALL views, including:
- Split GROUP BY over UNION ALL view
- Push-down join into UNION ALL view
A UNION can
be treated as a partitioned table only if it satisfies all of the following
constraints:
- It contains only one or more UNION ALL.
- Each arm of the UNION has only one table in its
FROM clause, and
that table is a physical base table.
- No arm of the UNION has a DISTINCT, a RANK, an aggregate
function, or a GROUP
BY clause.
- Each item in the SELECT clause within each arm
of the UNION is a
column.
- The sequence of data types for the columns in the SELECT list of the first UNION arm is identical to
the sequence in each subsequent arm of the UNION.