UNION ALL views can be used to improve load performance where it is too expensive to maintain secondary indexes for all rows in a table. Sybase IQ lets you split the data into several separate base tables (for example, by date). You load data into these smaller tables. You then join the tables back together into a logical whole by means of a UNION ALL view, which you can then query.
This strategy can improve load performance, but may negatively impact the performance of some types of queries. Most types of queries have roughly similar performance against a single base table or against a UNION ALL view over smaller base tables, as long as the view definition satisfies all the constraints described in “Optimizing queries that reference UNION ALL views”. However, some types of queries, especially those involving DISTINCT or involving joins with multiple join columns, may perform significantly slower against a UNION ALL view than against a single large base table. Before choosing to use this strategy, determine whether the improvements in load performance are worth the degradation in query performance for your application.
UNION ALL views can be efficient to administer. If the data is partitioned by month, for example, you can drop an entire month’s worth of data by deleting a table and updating the UNION ALL view definition appropriately. You can have many view definitions for a year, a quarter, and so on, without adding extra date range predicates.
To create a UNION ALL view, choose a logical means of dividing a base table into separate physical tables. The most common division is by month.
For example, to create a view including all months for the first quarter, enter:
CREATE VIEW
SELECT * JANUARY
UNION ALL
SELECT * FEBRUARY
UNION ALL
SELECT * MARCH
UNION ALL
Each month, you can load data into a single base table—JANUARY, FEBRUARY, or MARCH in this example. Next month, load data into a new table with the same columns, and the same index types.
For syntax details, see UNION operation in the Reference: Statements and Options.
You cannot perform an INSERT...SELECT into
a UNION ALL view. UNION ALL operators
are not fully parallel in this release. Their use may limit query parallelism.
