Ok, thank you Karol. Greetings.
Walter. On Mon, Aug 24, 2015 at 2:32 PM, [email protected] [firebird-support] <[email protected]> wrote: > > > Hi, > > as i say previously you can not do this > as index can not be updated if something was changed in ASIENTOSCAB. > And any subseclect is not recognized by plan parser as expression index def > > > regards, > Karol Bieniaszewski > > *From:* mailto:[email protected] > <[email protected]> > *Sent:* Monday, August 24, 2015 7:55 PM > *To:* [email protected] > *Subject:* Re: [firebird-support] Expression index for use with the GROUP > BY clause > > > > 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, [email protected] > [firebird-support] <[email protected]> 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:[email protected] >> <[email protected]> >> *Sent:* Monday, August 24, 2015 7:28 PM >> *To:* [email protected] >> *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. >> >> >> >> > > > >
