Have tested and the difference is still enormous, some factor 10.
As predicted the added between is slightly quicker than the union
construction and
thanks for that tip. This is in fact quite useful for me as currently
I have to convert
a OR SQL in code (VB) to the UNION and as you can imagine that can be quite
tricky, particularly if there is an order by. So, I will change this
code now to add
the between instead and that will be a lot simpler.

RBS

On 7/16/12, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>>Firebird 1.5 classic on Windows.
>>Is it possible to have a SQL with union all and an order by that includes a
>> field that is not in the selects?
>
> I'd be very surprised if this was possible, logically you sort a result set
> and the result set that does not contain your field. If it wasn't for the
> union, it is at least thinkable (for people like me without knowledge of
> Firebird internals) that the sorting could be done during the selection of
> records and that it could sort on the field. Though it should be simple for
> you to test to find out whether it is possible or not.
>
>>In case you wondered why I used a union here, it is as I found it be a lot
>> faster than using an OR.
>
> That must either be coincidental or because the PLAN changes. My guess is
> that your selectivity indicates that using an index is marginally better
> than using NATURAL, but that this changes once you introduce OR (it isn't
> generally quicker to use UNION than OR). Since Firebird doesn't have
> histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or
> 'bi'), this may be a wrong choice.
>
> If my guess is correct, then the following might make your query quick
> again:
>
> select fieldA, fieldB from table1
> where (fieldA starting with 'bd'
> or fieldA starting with 'bi')
> and fieldA between 'bd' and 'bj')
> order by fieldA asc, fieldC asc
>
> HTH,
> Set
>