> CREATE INDEX FAC_COM_IDX3 ON FAC_COM (ID_PROVEEDOR);
> CREATE INDEX FAC_COM_IDX4 ON FAC_COM COMPUTED BY (ABS(SALDO));
>
>
> select sum(saldo) from fac_com where (ABS(SALDO)>0) and (id_proveedor =
> :id_proveedor)
>
> I get:
>
> Plan
> PLAN (FAC_COM INDEX (FAC_COM_IDX3))
>
> Adapted Plan
> PLAN (FAC_COM INDEX (FAC_COM_IDX3))
>
> Shouldn't FB use FAC_COM_IDX4 also? (I'm using FB 2.1.1)

I believe you are right, and that the following plan should be possible:

PLAN (FAC_COM INDEX (FAC_COM_IDX3, FAC_COM_IDX4))

Note that just because a plan is possible, doesn't mean the optimiser
will choose that plan, but you can always add the plan to the end of
your query to check it.

Perhaps the statistics on FAC_COM_IDX4 are unfavourable. An optimiser
is basically a path costing tool that estimates how long each
potential solution would take and chooses the one *it* *believes* will
be the cheapest. If the statistics are incorrect, then this can skew
the estimation and the wrong plan could be used. Or perhaps the
statistics on FAC_COM_IDX3 are so good, that it doesn't bother reading
in yet another index and building yet another bitmap.

Try a set statistics on both indices and see whether the plan changes.

Also, I don't know whether the fact FAC_COM_IDX4 is an expression
index is significant? Someone with more experience with 2.x may be
able to spread more light on the area.


Adam