>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

not :( the plan order not accept 2 index
order IndexOnFieldAAndFieldBAndFieldC, IndexOnFieldAAndFieldBAndFieldC > ERROR

but this is a little better :
order IndexOnFieldAAndFieldBAndFieldC index (IndexOnFieldAAndFieldBAndFieldC))

a little because it's not really fast (around 500 ms when normally it's take only 10 ms)

>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

no, not work too :(

>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

WONDERFULL !!!! it's work, 10 ms !

only one thing :

1/ the record are not ordered so i need to add this

SELECT First 201 FieldC
FROM Combined
order by FieldC

it's work fast because here only 200 + 200 record to order


!! MANY THANKS svein !!

stephane

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> 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
>