RE: Better SQL performance with UNION?

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

Reeve, the surprise for me is that the optimizer did not use index-only
access for all the SELECTs - seems that would help.

The bit about EVIs makes sense - EVIs over columns with high cardinality
could have as many entries to process as a regular index.

Trigger idea soulds neat, too. Index over the finder file gets you there
almost instantly, not after multiple seconds of building the temp result
file - I mean, do you build the finder file now or later?

Anyway, glad to have had a chance to test out some things.

Vern

At 08:26 AM 9/21/02 -0400, you wrote:
> Vern, thanks for taking a kick at the can; I'm not surprised at your
> results.  I tried CREATE INDEX on each of the columns and (with STRDBG) and
> the Optimizer indicated it used the index.  It's time for a real test, I
> guess...
>
> I thought about EVI's but I thought I read (in the "Query Performance and
> Optimization" manual?) they're better on columns with a relatively small
> number of values.
>
> In thinking about an alternative solution, I've come up using with a trigger
> on the PF to write cross-reference entries to a finder file.  But it's so
> "System/36"-like it makes me gag.  It could be ITER...
>
> Thanks,
> reeve