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.
Sybase IQ includes patented optimizations for UNION ALL views, including:
Split group by over union all view
Push-down join into union all view
Should you need to adjust performance for queries that reference UNION ALL views, you might want to set the Join_Preference database option, which affects joins between UNION ALL views. For details of these options, see Chapter 2, “Database Options,” in the Sybase IQ Reference Manual.
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.
See also “SELECT statement,” in the Sybase IQ Reference Manual.
