Hi:

Thanks for your interest.

What I need is that the PLAN is:

PLAN JOIN (CAB INDEX (FCAJA_CAB_IDX_FECHA),DET
INDEX(RDB$PRIMARY33,RDB$FOREINGN2))

and not as at the moment it Works:

PLAN JOIN (DET INDEX (RDB$FOREIGN2),CAB
INDEX(RDB$PRIMARY31,FCAJA_CAB_IDX_FECHA))

You have some solution?

Edgar Brítez
Sebaot Software
Asunción - Paraguay

-----Mensaje original-----
De: Svein Erling Tysvaer
[mailto:svein.erling.tysvaer@...]
Enviado el: Martes, 17 de Junio de 2003 03:57 a.m.
Para: firebird-support@yahoogroups.com
Asunto: Re: [firebird-support] This is a bug ??????

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

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/