Hello Karol Thank you for your answer.
The query is a very simplified one, just for show the idea. I can create an index as the following: CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY ( CAST(ASI_ANOEJE AS CHAR(5)) || CAST(ASI_CODSUC AS CHAR(5)) || CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) || ASI_NUMCUE || ASI_NUMSUB ); But is is not used in the PLAN. I had tried several alternatives but without success until now. Using CAST(), without using CAST() and so on. I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can avoid create it, but I need rows grouped by that column. ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where ASIENTOSCAB is the parent. Greetings. Walter. On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl [firebird-support] <firebird-support@yahoogroups.com> wrote: > > > Hi, > > No – you can not create single index on more then one table > But you really need it? > How big is resultset? I do not see any filter in this query no HAVING nor > WHERE > > regards, > Karol Bieniaszewski > > *From:* mailto:firebird-support@yahoogroups.com > <firebird-support@yahoogroups.com> > *Sent:* Monday, August 24, 2015 7:28 PM > *To:* firebird-support@yahoogroups.com > *Subject:* [firebird-support] Expression index for use with the GROUP BY > clause > > > > Hello everybody > > I had the following query: > > SELECT > D.ASI_ANOEJE, > D.ASI_CODSUC, > EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES, > D.ASI_NUMCUE, > D.ASI_NUMSUB > FROM > ASIENTOSDET D > JOIN > ASIENTOSCAB C > ON D.ASI_CODSUC = C.ASC_CODSUC AND > D.ASI_IDECAB = C.ASC_IDENTI > GROUP BY > D.ASI_ANOEJE, > D.ASI_CODSUC, > EXTRACT(MONTH FROM C.ASC_FECHAX), > D.ASI_NUMCUE, > D.ASI_NUMSUB > > Is it possible to have an expression index for use here with the GROUP BY > clause? > > The PLAN is the following: > > PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB))) > > As you can see there is a SORT there caused by the GROUP BY clause. > > ASI_ANOEJE is SMALLINT > ASI_CODSUC is SMALLINT > ASC_FECHAX is DATE > ASI_NUMCUE is VARCHAR(16) > ASI_NUMSUB is CHAR(5) > > I'm using Firebird 2.5.4 > > Greetings. > > Walter. > > > > > >
