Re: Better SQL performance with UNION?

Reeve

What is the end result you're looking for? To list things in "address"
order by customer? The ORDER BY you have will not do this, I think - it'll
result in the various address component texts in order, followed by the
customer numbers(?) in order within each element's text - or something like
that.

Could you get the same result (report output?) by retrieving the values in
each row, using a where clause with ANDs between each component? Let the
app format the output.

But you may have a purpose I don't see yet.

About the UNONs, as you know, each is basically a separate query. The WHERE
clauses can be supported by individual indexes. 2 options come to mind -
indexes with both cmcust and the selection field in them. This could result
in an index-only access plan, since the data is all in the index. The
optimizer might not use this, though, because of the literl, or because of
the UNION- I don't remember.

TThe other idea is to create EVIs (encoded vector indexes). These are very
fast for record selection.

Be sure to use parameter markers (host variables) - then the access plan is
not reoptimized (as often, anyway) with different values passed in.

Did you really mean 500,000 columns? Maybe rows? I hope. Maximum row
_length_ is 32,700 such. And you can have that many 1-character columns, at
most.

At 12:05 AM 9/20/02 -0400, you wrote:

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