The engine does what you ask it to do, Stephane, it just needs to use an index twice if you want two versions. Hence, I suggest you try:

Select First 201 Skip 0
FieldC
FROM BigTable
WHERE
FieldA IN ('FR', 'US') AND
FieldB = 56
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC, IndexOnFieldAAndFieldBAndFieldC)
ORDER BY FieldA,
FieldB,
FieldC

Now, I actually doubt this will work (I'm more used to PLAN SORT than PLAN ORDER), and I would expect you to have to revert to one of the two options below:

1st option:
Select First 201 Skip 0
FieldC
FROM BigTable
WHERE
(FieldA = 'FR' AND
FieldB = 56) OR
(FieldA = 'US' AND
FieldB = 56)
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC) /* I don't know whether you have to repeat the index here (like above) */
ORDER BY FieldA,
FieldB,
FieldC

2nd option:
WITH FR as
(Select First 201 Skip 0
FieldA, FieldB, FieldC
FROM BigTable
WHERE
FieldA = 'FR' AND
FieldB = 56
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC)
ORDER BY FieldA,
FieldB,
FieldC),
US as
(Select First 201 Skip 0
FieldA, FieldB, FieldC
FROM BigTable
WHERE
FieldA = 'US' AND
FieldB = 56
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC)
ORDER BY FieldA,
FieldB,
FieldC),
Combined as(
SELECT * FROM FR
UNION
SELECT * FROM US)

SELECT First 201 FieldC
FROM Combined

HTH,
Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of svanderclock
Sent: 8. desember 2009 10:46
To: firebird-support@yahoogroups.com
Subject: [firebird-support] how to do union

Hello,

i already ask a question about this, but this is a little different so i rather prefer to start a new thread

i need to do this Query

Select First 201 Skip 0
FieldC
FROM BigTable
WHERE
FieldA = 'FR' AND
FieldB = 56
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC)
ORDER BY FieldA,
FieldB,
FieldC

this work ok (< 50 ms) but now i need also to do

WHERE
FieldA = 'FR' OR FieldA = 'US'

big problem : very very slow (> 6 seconds)

so the idea is to do an "union" :

Select First 201 Skip 0
FieldC
FROM BigTable
WHERE
FieldA = 'FR' AND
FieldB = 56
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC)
ORDER BY FieldA,
FieldB,
FieldC

Union

Select First 201 Skip 0
FieldC
FROM BigTable
WHERE
FieldA = 'US' AND
FieldB = 56
PLAN (BIGTABLE order IndexOnFieldAAndFieldBAndFieldC)
ORDER BY FieldA,
FieldB,
FieldC

normally is can not take more than 100 ms to do it !
but it's not work :( probably i write badly the union ?

i must manually do the union in the software, mean firt do the first query an retrieve the data, then do the second query and retrieve the data, then mix the data myself... is it normal ? why the engine can not simply do what we ask him : the simple union ?

thanks for all
stephane