Better SQL performance with UNION?

This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I’m trying to figure out how to get better performance using UNION.  The
interactive application uses embedded SQL and does a lookup over multiple
columns in one table.  SQL creates a temporary file and appears to copy the
records into it.

The number of columns will vary from 3,000 to 500,000 (but I have other
applications with much larger record counts and the potential for many more
UNION’s).

Here’s what I’ve done so far with STRSQL (where “ABC” is some user-specified
search argument to be substituted in the embedded SQL):

SELECT cmcust,cmname,'Name' FROM arp001 WHERE cmname >= 'ABC'
UNION
SELECT cmcust,cmadr1,'Adr1' FROM arp001 WHERE cmadr1 >= 'ABC'
UNION
SELECT cmcust,cmadr2,'Adr2' FROM arp001 WHERE cmadr2 >= 'ABC'
UNION
SELECT cmcust,cmcity,'City' FROM arp001 WHERE cmcity >= 'ABC'
UNION
SELECT cmcust,cmzip, 'Zip'  FROM arp001 WHERE cmzip  >= 'ABC'
UNION
SELECT cmcust,cmabbr,'Abbr' FROM arp001 WHERE cmabbr >= 'ABC'
                                        and   cmabbr <> cmname
ORDER BY 2,cmcust
OPTIMIZE FOR 20 ROWS


In the real world, I’d use a multi-format logical and response time would be
sub-second.  SQL doesn’t understand multi-format logicals and I’d like to do
something along the lines of CREATE INDEX to improve performance.

I’d be grateful for a suggestion or a reference…

Thanks,
Reeve

--