HP 3000 Manuals

ALLBASE/SQL Performance Guidelines

Using UNION 

When using UNION in your queries, make sure you avoid data conversions,
and be sure to define indexes on appropriate columns.

Avoiding Conversions 

SQLCore checks the data types of each select list in a UNION query, and
determines the result data type.  For all select lists that have data
types different than the result data type, a conversion is carried out if
the types are compatible.  Refer to the description of the SELECT
statement in the "SQL Statements" chapter of the ALLBASE/SQL Reference 
Manual for a table showing the kinds of conversions.

The following example requires conversions:

     CREATE TABLE T1 (Item CHAR(40), Price SMALLINT)
     CREATE TABLE T2 (Item CHAR(40), Price INTEGER)
     CREATE TABLE T3 (Item CHAR(40), Price DECIMAL(10,2)
     CREATE TABLE T4 (Item CHAR(40), Price FLOAT)

     SELECT Item, Price
     FROM T1
     UNION
     SELECT Item, Price
     from T2
     UNION
     SELECT Item, Price
     FROM T3
     UNION
     SELECT Item, Price
     FROM T4

Since the result data type in this UNION is FLOAT, sources 1, 2, and 3
require conversion to FLOAT, which is the largest common denominator
type.  Now if all columns were of the same type--for example, FLOAT--no
conversions would be required and the performance of such a query would
be faster than the conversion example.

Defining Indexes for UNION Queries 

Each source SELECT in a UNION query is optimized individually, and
SQLCore tries to pick the best access method for each source.  Therefore,
you should create indexes on all sources, if possible, to maximize
performance.  The result of the UNION is not optimized.

If you know that there are no duplicate rows generated by the query, or
if you do not need to exclude duplicate rows from the result, the UNION
ALL form is faster than UNION, because it does not sort the query result.

MPE/iX 5.0 Documentation