Hi,

we have the following query within a stored procedure.
ZU is a input parameter which can be NULL.
The meaning of NULL is --> don't pay attention to ZU

KA, KG and ZU are foreignkey-fields and have an index.
Table BU has about 100k records.

This is the way we solve this:

if (ZU is null) then ZU = -1;
for
select KA, sum(ValNet)
from BU
where KG = :KeyValue and (:ZU = -1 or Zu = :ZU)
group by KA
into :KA, :SumNet
do
suspend;

This solution works but the plan is not optimal:
PLAN (BU ORDER RDB$FOREIGN104) --> which is field KA --> just few
distinct values

Question:
Is there a better way doing this?
The goal is to force Firebird to use index KG and maybe also index ZU

On the other hand IBExpert shows only 16 indexed reads (which is the
number of KG not KA).
Why this? Is index KG used without showing in my tool (IBExpert)?

Could anybody enlighten me.


Regards,

Josef



[Non-text portions of this message have been removed]