Are you sure your plan is better or even different to Firebirds? I
mean, I do know that the order of tables in the plan matters and that
it matters which indexes are used, but I haven't heard about the order
of indexes within one table being of any importance. Firebird anyway
builds a bitmap rather than traversing all records in every index
sequentially.

What is the percentage of records with CRE.CRED_BOL_TRAITE =
UDF_FALSE()? And what is the percentage of records with
CRE.CRED_CDE_RAPPEL IS NULL? If it is a large percentage, I would
expect the following SQL to be your best bet:

SELECT SUM(PVT.PAYVENTE_MNT_MNT)
FROM TBL_CREDIT CRE
JOIN TBL_PAYER_VENTE PVT ON (PVT.PAYVENTE_NUM_VENTE_PK =
CRE.CRED_NUM_VENTE_PK)
WHERE
(CRE.CRED_NUM_CREDITEUR_FK = 1) AND
(PVT.PAYVENTE_NUM_TYPPAIE_PK = 4) AND
(2=0 or (
(CRE.CRED_BOL_TRAITE = UDF_FALSE()) AND
(CRE.CRED_CDE_RAPPEL IS NULL)))

In general, I would hope for this to create a plan starting with the
most selective of CRE.CRED_NUM_CREDITEUR_FK and
PVT.PAYVENTE_NUM_TYPPAIE_PK, and then link to the other table using
the field in the JOIN clause. Sometimes it may use both, and then it
may be worth trying to remove one of them through the +0 trick or
something.

In general, try to avoid forcing a plan, as far as I know none of the
optimizer experts normally do this. Using 2=0 or something like that
is the standard way to prevent the use of certain indexes, and when
some of the indexes will have poor selectivity in your particular
query, you may want to do this.

Another thing is that aggregate queries may have to use lots of
records in their calculation, and may appear slow even though they
return few rows.

HTH,
Set

--- In firebird-support@yahoogroups.com, "dry250" wrote:
> Hi,
>
> First, i am using FireBird 1.5.2 on Windows XP.
>
> I am optimizing my SQL queries, by searching the best plan for the
> slowest queries.
> I am meeting a little problem when I run this query :
>
> SELECT
> SUM(PVT.PAYVENTE_MNT_MNT)
>
> FROM TBL_CREDIT CRE
> JOIN TBL_PAYER_VENTE PVT ON (PVT.PAYVENTE_NUM_VENTE_PK =
> CRE.CRED_NUM_VENTE_PK)
>
> WHERE
> (CRE.CRED_NUM_CREDITEUR_FK = 1) AND
> (CRE.CRED_BOL_TRAITE = UDF_FALSE()) AND
> (CRE.CRED_CDE_RAPPEL IS NULL) AND
> (PVT.PAYVENTE_NUM_TYPPAIE_PK = 4)
>
> PLAN JOIN (
> CRE INDEX (FK_CREDIT_CLIENT, IDX_CREDIT_TRAITE,
> IDX_CREDIT_RAPPEL),
> PVT INDEX (FK_PAYERVENTE_VENTE)
> )
>
> What ever the order of "FK_CREDIT_CLIENT, IDX_CREDIT_TRAITE,
> IDX_CREDIT_RAPPEL" in my forced plan, Firebird always takes this
> plan :
>
> PLAN JOIN (
> CRE INDEX
> (IDX_CREDIT_RAPPEL,FK_CREDIT_CLIENT,IDX_CREDIT_TRAITE),
> PVT INDEX (FK_PAYERVENTE_VENTE)
> )
>
> (Same result if I don't specify any plan and if I change the order
> of conditions in the WHERE clause : always the same plan used)
>
> I know my plan is better than fireBird's plan and I would like to
> force fireBird to use mine.
> Is there an solution ?