Hi Edgar!

Arno might be interested in your report, but I think you would have to add
information about index selectivity and size of tables. It is a complex
calculation that decides which plan to use, and you often have to tweak it.
I do not understand what you mean by "adapted plan" - I've never heard of
such a thing, but the problem with the second plan is that it uses both the
primary key as well as another index within the plan. What you want to
have, is for it to use only the primary key index. If I guess correctly,
this means that you could improve performance by changing your query to:

select
cab.asiento,det.importe_movimiento,det.movimiento,det.codigo_cuenta
from fcaja_cab cab
inner join fcaja_det det on det.asiento=cab.asiento
where (cab.fecha_asiento = '11/02/2002' or 2=0) and
det.codigo_cuenta='010102010003'

Try and see how this works.

HTH,
Set

At 11:18 16.06.2003 -0400, you wrote:

>Hi:
>
>I believe that I found a BUG in firebird 1,03 and 1,5 and it seems to me
>enough serious because affects the yield of certain type of querys
>
>Bug would be sgte.
>
>I have a table head and another detail
>
>When I make a query that takes part columns type to integer (in the head
>and the detail) the query works OK.
>
>The problem this when in the detail the query with a condition is made
>that includes a column type to char.
>
>Example:
>
>select
>cab.asiento,det.importe_movimiento,det.movimiento,det.codigo_cuenta
>from fcaja_cab cab
>inner join fcaja_det det on det.asiento=cab.asiento
>where cab.fecha_asiento = '11/02/2002' and det.nro_compra=1
>
>The plan that uses is:
>
>Plan
>PLAN JOIN (CAB INDEX (FCAJA_CAB_IDX_FECHA),DET INDEX
>(RDB$PRIMARY33,FCAJA_DET_IDX1))
>
>Adapted Plan
>PLAN JOIN (CAB INDEX (FCAJA_CAB_IDX_FECHA),DET INDEX
>(PK_FCAJA_DET,FCAJA_DET_IDX1))
>
>
>As they see first looks for in the head and soon it looks for in the
>detail.
>
>Ok Performance!!!!!!!!
>
>
>If change the query:
>
>select
>cab.asiento,det.importe_movimiento,det.movimiento,det.codigo_cuenta
>from fcaja_cab cab
>inner join fcaja_det det on det.asiento=cab.asiento
>where cab.fecha_asiento = '11/02/2002' and
>det.codigo_cuenta='010102010003'
>
>
>The plan that uses is:
>
>Plan
>PLAN JOIN (DET INDEX (RDB$FOREIGN2),CAB INDEX
>(RDB$PRIMARY31,FCAJA_CAB_IDX_FECHA))
>
>Adapted Plan
>PLAN JOIN (DET INDEX (FK_FCAJA_DET_FCUENTAS),CAB INDEX
>(PK_FCAJA_CAB,FCAJA_CAB_IDX_FECHA))
>
>
>As they see first looks for in the DETAIL!!!!! And soon looks for in
>the head which causes that the query takes very slow because it are
>first all registry of the detail that umplan with the condition and soon
>leak by the condition of the head. Very bad performance!!!!!!!!!!!
>
>
>In the first case read 1 row in head and 1 row in detail. Result 1 row
>(according to IBexpert)
>
>In the second case read 5789 rows in detail and 1 in head. Result 1 row.
>
>As they imagine as it grows the detail more takes the query.
>
>Somebody knows where I must report this Bug and preferably in Spanish my
>then English is very bad