Svein Erling,

The Feribird plan for this select is:

PLAN SORT (JOIN (I INDEX (FK_BS_IDX_COD_GRP,BS_IDX_COD_BASE_IDX),T INDEX
(PK_BS_TRM)))

This select takes 23.294,00 ms to execute with GROUP BY T.TRM clause and
30,00 ms without GROUP BY TRM clause. So, I'm trying to do Firebird to use
BS_TRM_TRM_IDX for T.TRM column too. I'm trying the following plan:

PLAN JOIN( T ORDER BS_TRM_TRM_IDX, T INDEX (PK_BS_TRM), I INDEX
(FK_BS_IDX_COD_GRP))

But, occurrs:

the table is referenced twice; use aliases to differentiate

table BS_TRM is referenced more than once in plan; use alias to distinguish.

Your plan works, but takes 23.985,00 ms.

Thanks,

Rafael Santini

----- Original Message -----
From: "Svein Erling" <svein.erling.tysvaer@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, May 11, 2004 5:38 AM
Subject: [firebird-support] Re: PLAN


> Rafael, my impression is that most people try to avoid writing
> explicit plans and trust Arno (mr. Optimizer) to choose the best one
> possible for them. In circumstances when he chooses a non-optimal
> plan, the question is normally how to prevent using a certain index,
> rather than to actually add another index to the plan.
>
> I guess that BS_TRM_TRM_IDX cannot be used together with the indexes
> you already mention (assuming you have tried) and most likely you
> would only get worse performance if you tried forcing it using
> something like:
>
> PLAN JOIN( T ORDER(BS_TRM_TRM_IDX), I INDEX (FK_BS_IDX_COD_GRP,
> COD_TRM_IDX))
>
> (possibly ommitting either of the indexes for BS_IDX)
>
> On the positive side: The index you already supply seems OK, at least
> if the selectivity for the field I.COD_GRP is good.
>
> Good luck in learning more about indexes. 17 May at 1:45pm I will
> attend Arnos lecture about the optimizer at the Fulda conference.
>
> **************************************************************
> *** Set ***
> *** ***
> *** I support Firebird, I am a Firebird Foundation member ***
> *** Join today at http://www.firebirdsql.org/ff/foundation ***
> **************************************************************
>
> --- In firebird-support@yahoogroups.com, "Rafael Santini" wrote:
> > Hi,
> >
> > I need help with this:
> >
> > select T.TRM from BS_IDX I, BS_TRM T
> > where (I.COD_BASE = ? and I.COD_GRP = ?) and T.COD = I.COD_TRM
> > group by T.TRM
> > plan join (I INDEX (FK_BS_IDX_COD_GRP), T INDEX (PK_BS_TRM))
> >
> > The index for T.TRM column is BS_TRM_TRM_IDX. How to add this index
> to PLAN
> > clause ?
> >
> > Thanks,
> >
> > Rafael Santini
>
>
> Yahoo! Groups Sponsor
> ADVERTISEMENT
>
>
>
>
>
> --------------------------------------------------------------------------
------
> Yahoo! Groups Links
>
> a.. To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> b.. To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>
>
>
>
> --------------------------------------------------------------------------
------
> Esta mensagem foi verificada pelo E-mail Protegido Terra.
> Scan engine: VirusScan / Atualizado em 10/05/2004 / Versão: 1.5.2
> Proteja o seu e-mail Terra: http://www.emailprotegido.terra.com.br/
>
>
> --------------------------------------------------------------------------
------
> E-mail classificado pelo Identificador de Spam Inteligente.
> Para alterar a categoria classificada, visite
http://www.terra.com.br/centralunificada/emailprotegido/imail/imail.cgi?+_u=santini.lista&_l=1084264698.723110.3896.laranjal.terra.com.br